topic how to read data from rest client API in json and how to load that json data into mysql table in Talend Studio

how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Wed, 27 Jun 2018 19:49:23 GMT

Hi All,

Can anyone help me? I'm doing integration for eCommerce with live data using talend, now what I want I need to pull Json data from HTTP rest client API and that Json data i need to load into mysql table. so for that what i need do, Can someone tell me?

Thanks,

Arpit

Re: how to read data from rest client API in json and how to load that json data into mysql table

fdenis — Thu, 28 Jun 2018 08:43:49 GMT

have a look on trestClient help using "f1".

Re: how to read data from rest client API in json and how to load that json data into mysql table

manodwhb — Thu, 28 Jun 2018 11:46:42 GMT

@arpiita,check the below link may help.

https://community.talend.com/t5/Design-and-Development/How-to-call-json-data-from-http-s-in-tRest-or-tRestclient/td-p/23101

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Mon, 02 Jul 2018 09:42:10 GMT

Hi Manohar,

Thanks for reply but I want to load data into MySQL, Can you please tell me how can I do that?

Thanks,

Arpit

Re: how to read data from rest client API in json and how to load that json data into mysql table

manodwhb — Mon, 02 Jul 2018 12:30:48 GMT

@arpiita,instead of tLogRow,use tMysqlOutput or tMysqlBulkExec for bulk loading.

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Tue, 03 Jul 2018 14:37:16 GMT

Hello All,

I have created a job for get json data from API and want load that response to mysql table but I'm not able to insert data. Can someone tell me where I am doing wrong.

it is my json response

{
"order": {
"id": 445157867567,
"email": "vikas.mishra+11@wear.com",
"closed_at": null,
"created_at": "2018-06-26T05:26:01-04:00",
"updated_at": "2018-06-26T05:26:01-04:00",
"number": 535,
"note": null,
"token": "505367ffcbb118eb755bfd9a7fdcf4e9",
"gateway": "shopify_payments",
"test": true,
"total_price": "108.88",
"subtotal_price": "90.00",
"total_weight": 0,
"total_tax": "8.88",
"taxes_included": false,
"currency": "USD",
"financial_status": "partially_paid",
"confirmed": true,
"total_discounts": "0.00",
"total_line_items_price": "90.00",
"cart_token": "ed5e3c5a5f26b46070714805d4689498",
"buyer_accepts_marketing": false,
"name": "#1535",
"referring_site": "https:\/\/tjstaging.shopi.com\/",
"landing_site": "\/",
"cancelled_at": null,
"cancel_reason": null,
"total_price_usd": "108.88",
"checkout_token": "42cad23ffda9194961bb5fd7e640cd7b",
"reference": null,
"user_id": null,
"location_id": null,
"source_identifier": null,
"source_url": null,
"processed_at": "2018-06-26T05:26:01-04:00",
"device_id": null,
"phone": null,
"customer_locale": "en",
"app_id": 580111,
"browser_ip": "114.143.208.90",
"landing_site_ref": null,
"order_number": 1535,
"discount_applications": [],
"discount_codes": [],
"note_attributes": [],
"payment_gateway_names": ["gift_card", "shopify_payments"],
"processing_method": "direct",
"checkout_id": 1299371032623,
"source_name": "web",
"fulfillment_status": null,
"tax_lines": [{
"price": "4.00",
"rate": 0.04,
"title": "NY STATE TAX"
}, {
"price": "4.50",
"rate": 0.045,
"title": "NY CITY TAX"
}, {
"price": "0.38",
"rate": 0.00375,
"title": "NY SPECIAL TAX"
}],
"tags": "",
"contact_email": "vikas.mishra+11@twear.com",
"order_status_url": "https:\/\/tjstaging.myshopify.com\/1914667055\/orders\/505367ffcbb118eb755bfd9a7fdcf4e9\/authenticate?key=5f7e542bd0183268b3809b07d7ff671d",
"admin_graphql_api_id": "gid:\/\/shopy\/Order\/445157867567",
"line_items": [{
"id": 889005899823,
"variant_id": 8775564099631,
"title": "Air Boxer Brief",
"quantity": 2,
"price": "45.00",
"sku": "10000331000:010001",
"variant_title": "Small \/ BLACK",
"vendor": "tjstaging",
"fulfillment_service": "manual",
"product_id": 880594714671,
"requires_shipping": true,
"taxable": true,
"gift_card": false,
"pre_tax_price": "90.00",
"name": "Air Boxer Brief - Small \/ BLACK",
"variant_inventory_management": "shopify",
"properties": [],
"product_exists": true,
"fulfillable_quantity": 2,
"grams": 0,
"total_discount": "0.00",
"fulfillment_status": null,
"discount_allocations": [],
"admin_graphql_api_id": "gid:\/\/shopify\/LineItem\/889005899823",
"tax_lines": [{
"title": "NY STATE TAX",
"price": "3.60",
"rate": 0.04
}, {
"title": "NY CITY TAX",
"price": "4.05",
"rate": 0.045
}, {
"title": "NY SPECIAL TAX",
"price": "0.34",
"rate": 0.00375
}],
"origin_location": {
"id": 354071412783,
"country_code": "US",
"province_code": "NY",
"name": "tjstaging",
"address1": "100 Broadway",
"address2": "Flr 11",
"city": "New York",
"zip": "10005"
}
}],
"shipping_lines": [{
"id": 356015833135,
"title": "Standard Shipping",
"price": "10.00",
"code": "Standard Shipping",
"source": "shopify",
"phone": null,
"requested_fulfillment_service_id": null,
"delivery_category": null,
"carrier_identifier": null,
"discounted_price": "10.00",
"discount_allocations": [],
"tax_lines": [{
"title": "NY STATE TAX",
"price": "0.40",
"rate": 0.04
}, {
"title": "NY CITY TAX",
"price": "0.45",
"rate": 0.045
}, {
"title": "NY SPECIAL TAX",
"price": "0.04",
"rate": 0.00375
}]
}],
"billing_address": {
"first_name": "Dev",
"address1": "11, Park Very",
"phone": null,
"city": "New york",
"zip": "10056",
"province": "New York",
"country": "United States",
"last_name": "Vikas",
"address2": "street-7\/09",
"company": null,
"latitude": null,
"longitude": null,
"name": "Dev Vikas",
"country_code": "US",
"province_code": "NY"
},
"shipping_address": {
"first_name": "Dev",
"address1": "11, Park Very",
"phone": null,
"city": "New york",
"zip": "10056",
"province": "New York",
"country": "United States",
"last_name": "Vikas",
"address2": "street-7\/09",
"company": null,
"latitude": null,
"longitude": null,
"name": "Dev Vikas",
"country_code": "US",
"province_code": "NY"
},
"fulfillments": [],
"client_details": {
"browser_ip": "114.143.208.90",
"accept_language": "en-US,en;q=0.9",
"user_agent": "Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/67.0.3396.87 Safari\/537.36",
"session_hash": "dc6785ce5522d54aa85a50025f8c40f1",
"browser_width": 1518,
"browser_height": 748
},
"refunds": [],
"payment_details": {
"credit_card_bin": "424242",
"avs_result_code": "Y",
"cvv_result_code": "M",
"credit_card_number": "•••• •••• •••• 4242",
"credit_card_company": "Visa"
},
"customer": {
"id": 514958458927,
"email": "vikas.mishra+11@tommyjohnwear.com",
"accepts_marketing": false,
"created_at": "2018-06-12T04:55:08-04:00",
"updated_at": "2018-06-30T03:07:09-04:00",
"first_name": "Dev",
"last_name": "Vikas",
"orders_count": 13,
"state": "enabled",
"total_spent": "2711.51",
"last_order_id": 445438918703,
"note": null,
"verified_email": true,
"multipass_identifier": null,
"tax_exempt": false,
"phone": null,
"tags": "",
"last_order_name": "#1541",
"admin_graphql_api_id": "gid:\/\/shopy\/Customer\/514958458927",
"default_address": {
"id": 578522644527,
"customer_id": 514958458927,
"first_name": "Dev",
"last_name": "Vikas",
"company": "",
"address1": "11, Park Very",
"address2": "street-7\/09",
"city": "New york",
"province": "New York",
"country": "United States",
"zip": "10056",
"phone": "",
"name": "Dev Vikas",
"province_code": "NY",
"country_code": "US",
"country_name": "United States",
"default": true
}
}
}
}

Thanks,

RestApi.PNG

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Wed, 04 Jul 2018 13:16:57 GMT

Hi All,

Can anybody look at this? I have created job from pulling data from API and store in Mysql but here one problem occurs that date unable to pares. can some tell me what I need to do?

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Wed, 04 Jul 2018 15:25:40 GMT

@rahall_2_0 can you please at this?

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Wed, 04 Jul 2018 15:26:29 GMT

@rhall

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Wed, 04 Jul 2018 17:04:23 GMT

In your Date Pattern column for the date field in your tExtractJSONFields schema, try using this date pattern....

"yyyy-MM-dd'T'HH:mm:ssXXX"

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Fri, 06 Jul 2018 13:51:10 GMT

Thanks a lot @rhall

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Fri, 06 Jul 2018 14:19:27 GMT

It looks like a couple of answers on this question have solved your problems. You should accept the answers that have solved your problems as the accepted solutions. This helps point people who have similar issues to the correct answer and acts as a payback for people who have given you answers.

It's not a good idea to add multiple questions to a single thread as it can get confusing. It is much better to distil you bigger questions into multiple smaller questions.

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Fri, 06 Jul 2018 16:27:22 GMT

Sure Richard!!

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Fri, 06 Jul 2018 16:29:26 GMT

And most of the time I do not get an answer.

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Fri, 06 Jul 2018 16:40:13 GMT

This question is a good example of a question that might normally get ignored. The reason is that it is too broad. I can't answer all of it without almost doing the job for you. It could be broken down into smaller questions like, "How can I use a REST service to retrieve JSON?", "How can I load data to MySQL?", "How can I convert from a String to a Date?", etc, etc. Many people who answer on here, also have their own quite complicated requirements to build jobs for. I have absolutely no issue in answering a concise question about something very specific, but I tend to ignore questions that look open ended. I don't have the time to go through all of the scenarios that I may need to and chances are I won't get the Accepted Solution. Some Talend developers are starting to use the metrics (Kudos and Accepted Solutions) as indicators to prospective customers that they are people in the community to notice. When you have a small amount of time to offer advice, you have to weigh up if it is worth it.

Re: how to read data from rest client API in json and how to load that json data into mysql table

Anonymous — Sat, 07 Jul 2018 08:13:18 GMT

Hi Richard,

I really appreciated your efforts. And I understood what you want to say.

Thanks