topic Re: API data to talend open studio in Data Quality

API data to talend open studio

modara — Fri, 15 Nov 2024 21:45:02 GMT

Hi there,

I am trying to get API calendar data to JSON format , here the configuration i have done. but it gives me error

in trestclient configuration,

in textractjsonfield configation

in textractjsonfield i eidt schema and do this,

error i got,

Starting job calendar_API_Test at 08:55 31/05/2023.

[statistics] connecting to socket on port 3731

[statistics] connected

Could not parse token starting at position 19. Expected ?, ', 0-9, *

[statistics] disconnected

Job calendar_API_Test ended at 08:56 31/05/2023. [Exit code = 0]

but i am well connected using post man, when i do postman it give me apt data like this,

{

"meta"

: {

"code"

200

"response"

: {

"holidays"

: [

{

"name"

"New Year's Day"

"description"

"New Year’s Day is the first day of the year, or January 1, in the Gregorian calendar."

"country"

: {

"id"

"bd"

"name"

"Bangladesh"

"date"

: {

"iso"

"2005-01-01"

"datetime"

: {

"year"

2005

"month"

"day"

}

"type"

: [

"Common local holiday"

if you need any help please do not hesitate , i really need to take data using api and convert to json then export to data base. this is my goal.

please help me to achieve

thanks

Re: API data to talend open studio

Anonymous — Wed, 31 May 2023 03:59:40 GMT

Can you output the response data on the console to see what data it returned?

tRestClient--main--tLogRow

Regards

Shong

Re: API data to talend open studio

modara — Wed, 31 May 2023 08:32:29 GMT

Hi Shong,

Thnaks for your reply, here is what i get,

i want to get calendar value via api then change to json and insert to database

thanks

Re: API data to talend open studio

Anonymous — Wed, 31 May 2023 09:49:21 GMT

Can you copy and paste the output here? I will try to reproduce the issue.

Re: API data to talend open studio

modara — Wed, 31 May 2023 11:01:28 GMT

Starting job calendar_API_Test at 16:58 31/05/2023.

[statistics] connecting to socket on port 3335

[statistics] connected

200|<?xml version="1.0" encoding="UTF-8"?>

<root><meta><code>200</code></meta><response><holidays><name>New Year's Day</name><description>New Year’s Day is the first day of the year, or January 1, in the Gregorian calendar.</description><country><id>bd</id><name>Bangladesh</name></country><date><iso>2005-01-01</iso><datetime><year>2005</year><month>1</month><day>1</day></datetime></date><type>Common local holiday</type><primary_type>Optional Holiday</primary_type><canonical_url>https://calendarific.com/holiday/bangladesh/new-year-day</canonical_url><urlid>bangladesh/new-year-day</urlid><locations>All</locations><states>All</states></holidays><holidays><name>Bangabandhu Homecoming Day</name><description>Bangabandhu Homecoming Day is a observance in Bangladesh</description><country><id>bd</id><name>Bangladesh</name></country><date><iso>2005-01-10</iso><datetime><year>2005</year><month>1</month><day>10</day></datetime></date><type>Common local holiday</type><primary_type>Observance</primary_type><canonical_url>https://calendarific.com/holiday/bangladesh/bangabandhu-homecoming-day</canonical_url><urlid>bangladesh/bangabandhu-homecoming-day</urlid><locations>All</locations><states>All</states></holidays><holidays><name>Eid al-Adha</name><description>Eid al-Adha (Id ul-Adha) is an Islamic festival falling on the 10th

[statistics] disconnected

here it outout pertial sample. as its a long words to exceed to paste here. i give you few

Re: API data to talend open studio

modara — Wed, 31 May 2023 13:33:11 GMT

Hi Shong,

this is the output from postman,

{

"meta": {

"code": 200

"response": {

"holidays": [

{

"name": "New Year's Day",

"description": "New Year’s Day is the first day of the year, or January 1, in the Gregorian calendar.",

"country": {

"id": "bd",

"name": "Bangladesh"

"date": {

"iso": "2023-01-01",

"datetime": {

"year": 2023,

"month": 1,

"day": 1

}

"type": [

"Common local holiday"

"primary_type": "Optional Holiday",

"canonical_url": "https://calendarific.com/holiday/bangladesh/new-year-day",

"urlid": "bangladesh/new-year-day",

"locations": "All",

"states": "All"

{

"name": "Bangabandhu Homecoming Day",

"description": "Bangabandhu Homecoming Day is a observance in Bangladesh",

"country": {

"id": "bd",

"name": "Bangladesh"

"date": {

"iso": "2023-01-10",

"datetime": {

"year": 2023,

"month": 1,

"day": 10

}

"type": [

"Common local holiday"

"primary_type": "Observance",

"canonical_url": "https://calendarific.com/holiday/bangladesh/bangabandhu-homecoming-day",

"urlid": "bangladesh/bangabandhu-homecoming-day",

"locations": "All",

"states": "All"

}

]

}

Re: API data to talend open studio

anselmopeixoto — Wed, 31 May 2023 18:36:24 GMT

Hi @Not defined Not defined

tRESTClient component by default converts the response to a DOM XML Document. So if you use the "body" column from the response schema, you could extract the elements from the response using a tXMLMap component, for example.

You could change this behavior by unchecking the option "Convert Response to DOM Document" on its Advanced settings.

Then you should be able to use tExtractJSONFields component, but I believe you should also change the "JSON field" to use the "string" column coming from tRESTClient response schema.

I hope this helps.

Best regards,

Anselmo

Re: API data to talend open studio

Anonymous — Thu, 01 Jun 2023 03:08:27 GMT

Hi @Not defined Not defined

As anselmopeixoto suggested, use tXMLMap to extract data from the returned Document, or you can use tRest or tHttpRequest to call the API, these two components returns a string response.

Regards

Shong

Re: API data to talend open studio

modara — Thu, 01 Jun 2023 03:32:31 GMT

Hi shong,

so please make me clear, should i use

tRest or tHttPRequest >>>>> tXMLMAP >>>>>tmysqlinput (Convert Response to DOM Document" on its Advanced settings.)

tXMLMap >>> tExtractJSONFields

I am really clear to make the diagram. please help me

as said earlier just want to convert API data to JSON then to MYSQL

thanks

Re: API data to talend open studio

Anonymous — Thu, 01 Jun 2023 03:48:22 GMT

tRestClient--main--tXMLMAP-->tMysqlOutput

if the returned string is XML format, use

tRest (or tHttpRequest)--main--tExtactXMLFields--tMysqlOutput

if the returned string is JSON format, use

tRest (or tHttpRequest)--main--tExtactJSONFields--tMysqlOutput

From your screenshot, i see it is XML format.

Re: API data to talend open studio

modara — Thu, 01 Jun 2023 14:39:47 GMT

Hi Shong,

I have came acrosse this with your help. Please guide me last step.

From talend i want to give instrustion to create table and column as per my output from xml

my xml output is,

statusCode|body

|200 |<?xml version="1.0" encoding="UTF-8"?>

<root>200New Year's DayNew Year’s Day is the first day of the year, or January 1, in the Gregorian calendar.bdBangladesh2005-01-01200511Common local holidayOptional Holidayhttps://calendarific.com/holiday/bangladesh/new-year-daybangladesh/new.........................

i dont understand can i create mysql table and column from talend or i have to create into mysql data base then point table from talend?

how can i map or identify column output from tXMLMAP to tMYSQL

please give me an example

thanks

Re: API data to talend open studio

modara — Thu, 01 Jun 2023 16:20:21 GMT

how can icreate table and map txmlmap with tmysql output as my data from postman is like this in xml format

{"meta":{"code":200},"response":{"holidays":[{"name":"New Year's Day","description":"New Year\u2019s Day is the first day of the year, or January 1, in the Gregorian calendar.","country":{"id":"bd","name":"Bangladesh"},"date":{"iso":"2005-01-01","datetime":{"year":2005,"month":1,"day":1}},"type":["Common local holiday"],"primary_type":"Optional Holiday","canonical_url":"https:\/\/calendarific.com\/holiday\/bangladesh\/new-year-day","urlid":"bangladesh\/new-year-day","locations":"All","states":"All"},

or in JSON

{

"meta": {

"code": 200

"response": {

"holidays": [

{

"name": "New Year's Day",

"description": "New Year’s Day is the first day of the year, or January 1, in the Gregorian calendar.",

"country": {

"id": "bd",

"name": "Bangladesh"

"date": {

"iso": "2005-01-01",

"datetime": {

"year": 2005,

"month": 1,

"day": 1

}

"type": [

"Common local holiday"

"primary_type": "Optional Holiday",

"canonical_url": "https://calendarific.com/holiday/bangladesh/new-year-day",

"urlid": "bangladesh/new-year-day",

"locations": "All",

"states": "All"

}

Re: API data to talend open studio

modara — Fri, 02 Jun 2023 06:08:09 GMT

hello,

please help me to sort this last part to create table in data base and map column

thc

Re: API data to talend open studio

Anonymous — Fri, 02 Jun 2023 08:00:38 GMT

You can create the table in DB and then specify the table name on tMysqlOutput or create the table directly on tMysqlOutput if the table does not exist. In the basic setting panel, select 'create table if not exist' option from the Action on table list, the table structure depends on the schema of tMysqlOutput component, so open the schema, define the fields, DB type and length.

Do you want to extract data from the returned string and store the data into DB table or store the returned string in DB table?

Regards

Shong

Re: API data to talend open studio

modara — Fri, 02 Jun 2023 09:25:52 GMT

Hi shong,

i can create a mysql table but need to know what column name i should give

and how the structude would be in talend so i can map column to mysql to send data from https://calendarific.com

I just want to take data from https://calendarific.com and import to table and column into mysql

iso please help me column maping for XML data

thanks

Re: API data to talend open studio

modara — Fri, 02 Jun 2023 09:27:21 GMT

I just want to take each each column data from xml to mysql in a table format

Re: API data to talend open studio

Anonymous — Fri, 02 Jun 2023 10:56:46 GMT

It depends on what data you want to store in DB, and extract these data from the returned string, I don't know which values you want to extract! Define the schema of tExtractXMLfields and link tExtractXMLfields to tMysqlOutput, the data extracted from the returned string on tExtractXMLfields will be inserted into DB table.

Re: API data to talend open studio

modara — Fri, 02 Jun 2023 12:05:24 GMT

Hi shong,

I want to import all filed or data points into mysql

i dont know how to structure or what component to use for XML format to mysql import

holidays,

name,

description,

country

name

date

iso

datetime

year

month

day

type,

primary_type,

canonical_url,

urlid,

locations,

states

what about left side? there be statuscode, body, string, response be same as there?

on right side should give all above data points or column name for mysql?

if so then how i can do heirachy type column name like

date

iso

datetime

year

month

day

and in textractxmpfield there is under basic there is a place call look xml filed " " what i should put in there?

thanks

Re: API data to talend open studio

modara — Sat, 03 Jun 2023 04:26:00 GMT

Hello,

I think i am almost closer to convert api data to output

here is my picture attached.

in tRESTClient component

intExtractXMLField

intXMLMap component

and log file or output

i jsut try to take two column name and discription to log or out. here is my JSON format

"response": {

"holidays": [

{

"name": "New Year's Day",

"description": "New Year’s Day is the first day of the year, or January 1, in the Gregorian

}}

please help me where i hav eot do loop and how can i take values in column. as you seen column names are there but no values.

if you need more info please do not hesitate. i need to complete this. please help or guide me.

thankx

Re: API data to talend open studio

Anonymous — Sun, 04 Jun 2023 01:42:03 GMT

tRestClient return a Document type be default, you don't need tExtractXMlField component, the job looks like:

tRestClient --main--tXMLMap--main--tLogRow.