Hi! I just set up the target-bigquery, but I'm get...
# troubleshooting
s
Hi! I just set up the target-bigquery, but I'm getting a key-error:
Key-error: RECORD
. Anyone ever have this, and if so how did you solve?
Full error:
Copy code
2022-04-06T21:21:43.911337Z [error    ] Loading failed                 code=2 job_id=dev_hubspot-to-bigquery message=CRITICAL ['Traceback (most recent call last):\n', '  File "/home/stephane/Documents/projects/meltano-projects/meltano/.meltano/loaders/target-bigquery/venv/lib/python3.9/site-packages/target_bigquery/__init__.py", line 129, in main\n    for state in state_iterator:\n', '  File "/home/stephane/Documents/projects/meltano-projects/meltano/.meltano/loaders/target-bigquery/venv/lib/python3.9/site-packages/target_bigquery/process.py", line 54, in process\n    for s in handler.handle_record_message(msg):\n', '  File "/home/stephane/Documents/projects/meltano-projects/meltano/.meltano/loaders/target-bigquery/venv/lib/python3.9/site-packages/target_bigquery/processhandler.py", line 179, in handle_record_message\n    nr = format_record_to_schema(nr, self.bq_schema_dicts[stream])\n', '  File "/home/stephane/Documents/projects/meltano-projects/meltano/.meltano/loaders/target-bigquery/venv/lib/python3.9/site-packages/target_bigquery/schema.py", line 354, in format_record_to_schema\n    record[k] = format_record_to_schema(record[k], bq_schema[k]["fields"])\n', '  File "/home/stephane/Documents/projects/meltano-projects/meltano/.meltano/loaders/target-bigquery/venv/lib/python3.9/site-packages/target_bigquery/schema.py", line 357, in format_record_to_schema\n    record[k] = [conversion_dict[bq_schema[k]["type"]](vi) for vi in v]\n', '  File "/home/stephane/Documents/projects/meltano-projects/meltano/.meltano/loaders/target-bigquery/venv/lib/python3.9/site-packages/target_bigquery/schema.py", line 357, in <listcomp>\n    record[k] = [conversion_dict[bq_schema[k]["type"]](vi) for vi in v]\n', "KeyError: 'RECORD'\n"] name=meltano run_id=e83dd4e4-e394-48c1-adc3-a0c308e4d651
d
This is a pretty common BQ target error and usually has to do with the catalog from the tap not fully defining all properties. You'll likely find
{}
when running --discover on the tap. To overcome this, you can define the empty properties and pass that with the --catalog.
p
@dan_ladd Please see if you have come across this error in linux installation
Conflicting dependencies
d
Seems setuptools was bumped in v12, so maybe try that one https://github.com/adswerve/target-bigquery/releases/tag/v0.12.0
s
Thanks!
So this would be an issue with my tap setup, not the gbq target?
p
@dan_ladd Updated the version now some other issue
2022-04-07T15:48:25.729926Z [error    ] Loading failed                 code=2 job_id=2022-04-07T154822--tap-klaviyo--target-bigquery message=CRITICAL ['Traceback (most recent call last):\n', '  File "/home/pranit/meltano-projects/meltano-projects/nhg/.meltano/loaders/target-bigquery/venv/lib/python3.8/site-packages/target_bigquery/__init__.py", line 129, in main\n    for state in state_iterator:\n', '  File "/home/pranit/meltano-projects/meltano-projects/nhg/.meltano/loaders/target-bigquery/venv/lib/python3.8/site-packages/target_bigquery/process.py", line 54, in process\n    for s in handler.handle_record_message(msg):\n', '  File "/home/pranit/meltano-projects/meltano-projects/nhg/.meltano/loaders/target-bigquery/venv/lib/python3.8/site-packages/target_bigquery/processhandler.py", line 179, in handle_record_message\n    nr = format_record_to_schema(nr, self.bq_schema_dicts[stream])\n', '  File "/home/pranit/meltano-projects/meltano-projects/nhg/.meltano/loaders/target-bigquery/venv/lib/python3.8/site-packages/target_bigquery/schema.py", line 359, in format_record_to_schema\n    record[k] = conversion_dict[bq_schema[k]["type"]](v)\n', "KeyError: 'RECORD'\n"] name=meltano run_id=9a0d7c7e-bc89-4df9-bcc0-c8cb30994ca0
d
@Stéphane Burwash Ideally in the future the target-bigquery would be try to handle these properties as a JSON string or better yet using the new JSON type. I think the snowflake target does this with it's VARIANT type. @pranit__warehows.io_, welcome to the party! That error is what this thread is about
s
Awesome thanks!
p
@dan_ladd I provided the catalog.json file, now it gives some json decoding error. My tap works well when I load in jsonl, need help with big query loader
@Stéphane Burwash Let me know if you are able to solve this issue
@cody_hanson If you can help us with some previous issue resolution or your take on this
@dan_ladd My catalogue file does define all properties and it's a valid json. Don't know what is going wrong. does table_config have to do anything with it? because that's the line error throws out in logs
d
It's not defining the nested properties here:
Copy code
"event_properties": {
            "type": "object",
            "properties": {}
          },
          "person": {
            "type": "object",
            "properties": {}
          }
p
I created this using singer discover tool, is there any other method to do that? @dan_ladd I also see that the schema is same defined for all streams
Also what if I don't want to parse it, just get stream as is and send it to big query, how can I achieve that ?
basically taps discover command doenst work as expected, what's the solution to this then?
cc: @espen_espelund
@cody_hanson Please check this thread, below is the error while loading to big query. Able to load and get json using
target-jsonl
. Catalog created using discover command and singer discover utility
Copy code
2022-04-07T15:48:25.729926Z [error    ] Loading failed                 code=2 job_id=2022-04-07T154822--tap-klaviyo--target-bigquery message=CRITICAL ['Traceback (most recent call last):\n', '  File "/home/pranit/meltano-projects/meltano-projects/nhg/.meltano/loaders/target-bigquery/venv/lib/python3.8/site-packages/target_bigquery/__init__.py", line 129, in main\n    for state in state_iterator:\n', '  File "/home/pranit/meltano-projects/meltano-projects/nhg/.meltano/loaders/target-bigquery/venv/lib/python3.8/site-packages/target_bigquery/process.py", line 54, in process\n    for s in handler.handle_record_message(msg):\n', '  File "/home/pranit/meltano-projects/meltano-projects/nhg/.meltano/loaders/target-bigquery/venv/lib/python3.8/site-packages/target_bigquery/processhandler.py", line 179, in handle_record_message\n    nr = format_record_to_schema(nr, self.bq_schema_dicts[stream])\n', '  File "/home/pranit/meltano-projects/meltano-projects/nhg/.meltano/loaders/target-bigquery/venv/lib/python3.8/site-packages/target_bigquery/schema.py", line 359, in format_record_to_schema\n    record[k] = conversion_dict[bq_schema[k]["type"]](v)\n', "KeyError: 'RECORD'\n"] name=meltano run_id=9a0d7c7e-bc89-4df9-bcc0-c8cb30994ca0
Let me know if you have come across this
d
Pranit, If you don't want to schema out the empty properties you would have to edit the tap or target yourself. You could either: • fork the tap to write those dynamic properties as a raw JSON string • fork target-bigquery to convert these instancese as a raw JSON string or use the new JSON type I alluded to before. If you don't feel comfortable doing that, I'd recommend just providing your own catalog as I've mentioned above.