Six Stars

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

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

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Fifteen Stars

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

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. 

Rilhia Solutions
15 REPLIES
Ten Stars

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

have a look on trestClient help using "f1".

Francois Denis

Don't forget to tag when it's "solved"!

Twelve Stars

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

Six Stars

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

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

 

 

Twelve Stars

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

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

Manohar B
Six Stars

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

RestApi.PNG 

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:010001Smiley Frustrated",
"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
}
}
}
}json.PNG

 

Thanks,

Six Stars

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

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? 

 

TalendJob.PNG

Six Stars

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

@rahall_2_0 can you please at this?
Six Stars

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

Fifteen Stars

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

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"
Rilhia Solutions
Six Stars

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

Thanks a lot @rhall_2_0

Fifteen Stars

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

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.

Rilhia Solutions
Six Stars

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

Sure Richard!!
Six Stars

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

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

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

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. 

Rilhia Solutions
Six Stars

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

Hi Richard,

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

Thanks