An export of data from BigQuery returns data in an unusable format. I have successfully used javascript to transform it into JSON that seems like it should work. However, I can't figure out what format the data should be in. I've tried to use something like the following with one to many on the import, specifying record for the path:
{"record": [
{"field1": "value1", "field2": "value2"},
{"field1": "value3", "field2": "value4"}
]
OR with just an array of objects (with no one to many) and other similar configurations
I think the easiest format is an array of objects (and no 'one to many').
[
{"field1": "value1", "field2": "value2"},
{"field1": "value3", "field2": "value4"}
]
If this is not working, then please provide more details in your post. For example, please share a screenshot of your import configuration, field -> column mappings, the resulting output file vs the expected output file, etc...
thanks for your help Scott, this still doesn't seem to work.
Here's the preview from my transformation after the export from BigQuery...
Here's my import set up for FTP...
The CSV generator helper doesn't seem to recognize the input data...
But the mapper does...
A file is created and sent to S3, but contains only a header and single blank line...
I am a rookie :-) What am I doing wrong?
This is tricky to explain here, and it might help to call support for more info, but it looks like the core issue is that each single "record" in your transformation script is being represented by an array of objects; and it would be much easier if each single record was just a single object.
At a very high level, a transformation script always executes on a single record at a time, and if you see an array of objects in your transformation input, then your export might not be setup in the best way possible.
Here are some things to consider.
- Did you set the "Path to records in HTTP response body" field so that integrator.io knows where the records are in an API response vs treating the entire API response as a single record?
- Instead of using a "Transformation" -> "JavaScript" to change the BigQuery export data, can you use a "Pre save page" hook, and then in your hook code make sure the data that you return is a simple array of objects. Hooks are different than transformations in that they always operate on arrays of objects (i.e. pages of records), and this is where you can reshape multiple records at once and remove complicated structures that span multiple records, etc... Here is some sample code to help.
function preSavePage (options) {
return {
data: [ {"field1": "value1", "field2": "value2"}, {"field1": "value3", "field2": "value4"} ]
}
}
