tBigQueryOutput Component cant Sync Schema

One Star

tBigQueryOutput Component cant Sync Schema

Hi,
I have a tFileInputDelimited component linked to a tBigQueryOutput component. The problem I have is when I run the job I get an error back from BigQuery saying the Schema type I am submitting does not match the destination table. I then took a look at the "Code" section behind the tBigQueryOutput component and it has set the filed types to all = string. See attached images.
However, if you look at the Schema of the input file and the tBigQueryOutput component they all show Strings and Dates and Bigdecimals etc.
The Error I get back:
{I removed some non essential parts from the below}
WARNING: Unable to find 'mime.types' file in classpath
Starting load job.
Exception in component tBigQueryOutput_2_tBQBE
java.lang.Exception: ............."schema":{"fields":[{"name":"Transaction_Id","type":"STRING"},{"name":"Sales_Date","type":"STRING"},{"name":"Item_Code","type":"STRING"},{"name":"Product_Category","type":"STRING"},{"name":"Transaction_TypeID","type":"STRING"},{"name":"Transaction_TypeDescription","type":"STRING"},{"name":"Sales_Rep","type":"STRING"},{"name":"Unit_Price","type":"STRING"},{"name":"Unit_Cost","type":"STRING"},{"name":"Sales_Value","type":"STRING"},......
"status":{"errorResult":{"message":"Provided Schema does not match Table ......

Possible Causes:
BigQuery only accepts data types of "string"; "float";"integer", "timestamp" etc. Talend does not have a "timestamp" datatype.
Could this be causing the problem? Anyone have the same issue and have a workaround.
Running TOS BigData
Version: 5.4.0
Build id: r110020-20131025-0636
Seventeen Stars

Re: tBigQueryOutput Component cant Sync Schema

Jepp, you should insert a tMap into your flow and change the datatypes from the output flow to what BigQuery only allows. After that you have to convert the types with the assignment expressions or you could use the tConvertType component.
One Star

Re: tBigQueryOutput Component cant Sync Schema

Jepp, you should insert a tMap into your flow and change the datatypes from the output flow to what BigQuery only allows. After that you have to convert the types with the assignment expressions or you could use the tConvertType component.

Hi jlolling
I tried to do that but tMap or tConvertType does not support the data type "timestamp" which BigQuery uses to identify dates. In fact I can see that any Date types are automatically changed to "String" before being submitted to BigQuery and this of course causes the Schema errors as String does not match Timestamp. BigQuery added the "timestamp" datatype in March 2013 so I find it strange that it is not supported in the tBigQueryOutput component thats why I am assuming I am doing something wrong or missing a setting somewhere.
Does the tBigQueryOutput component have a way of disabling the Schema it sends through to BigQuery OR alternatively is there a way to EDIT the automatic Date to String adjustments to change it to Date to Timestamp?
Note:
I tried editing the tBiqQueryOuput component directly in the xml file by commenting out the parts relating to SCHEMA but no luck, still tries to submit the schema
<!--<TEMPLATE_PARAM SOURCE="self.SCHEMA" TARGET="tBQOB.SCHEMA" />
<TEMPLATE_PARAM SOURCE="self.SCHEMA" TARGET="tBQBE.SCHEMA" /> -->

Any idea?
One Star

Re: tBigQueryOutput Component cant Sync Schema

Possible Workaround:
Instead of using the tBigQueryOuput component I used the tSystem component to run BigQuery commands through the bq command line tool. Just needed to install the command line tool from Google's Site and Python and then it worked perfectly.
Configuration for tSystem component:
Select the "Use Array Command" option
"cmd"
"/c"
"python C:/bigquery/bq/bq.py load --skip_leading_rows=1 --field_delimiter=\\t Dataset.TableName gs://bucketName/fileName.txt"
Note:
Had to add in the "\" in front of "\t" to make sure the command goes through as tab delimited.
Its effective but would have been cleaner inside tBigQueryOuput component.