I have a question that I have a hard time figuring out from the docs. I’m using

tap-clickup

and have a catalog schema for

task.tags

like this

"tags": {
            "items": {
              "properties": {
                "name": {
                  "type": [
                    "string",
                    "null"
                  ]
                },
                "tag_fg": {
                  "type": [
                    "string",
                    "null"
                  ]
                },
                "tag_bg": {
                  "type": [
                    "string",
                    "null"
                  ]
                },
                "creator": {
                  "type": [
                    "integer",
                    "null"
                  ]
                }
              },
              "type": "object"
            },

I want to write a selector for task.tags.name only. However there’s no documentation for how to deal with the fact that it’s an array. I though it might work like

select: [task.tags.name]

in my tap config in meltano.yml, but that didn’t do the trick. If I put it

select: [task.tags]

I always get the full tags object

yeah nested objects are tough. Most people say you should just load the data into your DW and then parse it there. Any reason you can't do that here?

Awesome appreciate your help @visch 😄 One challenge I appear to run into is that the dynamic nature of the clickup API responses makes the bigquery loader cough up on schema issues. Restricting the fields (and avoiding the deeply nested objects) has helped a lot working around those kind of problems

Could you post the error's bigquery is giving? The targets are different enough it's almost like the testing in the tap should test out the top 5 targets or so

They look like this

2021-12-22T20:11:26.264433Z [info     ] CRITICAL ['Traceback (most recent call last):\n', '  File "/Users/jrudolph/dev/mc/meshbarn/tractor/.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 "/Users/jrudolph/dev/mc/meshbarn/tractor/.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 "/Users/jrudolph/dev/mc/meshbarn/tractor/.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 "/Users/jrudolph/dev/mc/meshbarn/tractor/.meltano/loaders/target-bigquery/venv/lib/python3.9/site-packages/target_bigquery/schema.py", line 363, in format_record_to_schema\n    record[k] = conversion_dict[bq_schema[k]["type"]](v)\n', "KeyError: 'RECORD'\n"] cmd_type=loader job_id=clickup-to-bigquery name=target-bigquery run_id=432d8fe3-d497-4215-acdd-46a81a5e7d7b stdio=stderr
2021-12-22T20:11:26.338162Z [error    ] Loading failed                 code=2 job_id=clickup-to-bigquery message=CRITICAL ['Traceback (most recent call last):\n', '  File "/Users/jrudolph/dev/mc/meshbarn/tractor/.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 "/Users/jrudolph/dev/mc/meshbarn/tractor/.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 "/Users/jrudolph/dev/mc/meshbarn/tractor/.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 "/Users/jrudolph/dev/mc/meshbarn/tractor/.meltano/loaders/target-bigquery/venv/lib/python3.9/site-packages/target_bigquery/schema.py", line 363, in format_record_to_schema\n    record[k] = conversion_dict[bq_schema[k]["type"]](v)\n', "KeyError: 'RECORD'\n"] name=meltano run_id=432d8fe3-d497-4215-acdd-46a81a5e7d7b
2021-12-22T20:11:26.339047Z [info     ] ELT could not be completed: Loader failed cmd_type=elt job_id=clickup-to-bigquery name=meltano run_id=432d8fe3-d497-4215-acdd-46a81a5e7d7b stdio=stderr
ELT could not be completed: Loader failed

https://meltano.slack.com/archives/C01PKLU5D1R/p1638475917280700?thread_ts=1638427862.273600 may help

I also ran into this last week, and it relates to invalid schemas (just as you guessed @johannes_rudolph ) , I got it to work by updating schema.py with a try/catch .. its not a solution, but it let me see what was actually happening …. invalid schemas for the tap (shopify)

elif isinstance(record, dict):
        rc = record.copy()
        for k, v in rc.items():
            if k not in bq_schema:
                record.pop(k)
            elif v is None:
                pass
            elif bq_schema[k].get("fields"):
                # mode: REPEATED, type: NULLABLE || mode: REPEATED: type: REPEATED
                record[k] = format_record_to_schema(record[k], bq_schema[k]["fields"])
            elif bq_schema[k].get("mode") == "REPEATED":
                # mode: REPEATED, type: [any]
                record[k] = [conversion_dict[bq_schema[k]["type"]](vi) for vi in v]
            else:
                try:
                    record[k] = conversion_dict[bq_schema[k]["type"]](v)
                except Exception as e:
                    # RECORDS with [] empty payloads
                    record[k] = format_record_to_schema(
                        record[k], bq_schema[k]["fields"]
                    )

once the bugfix is applied it will hopefully resolve anyway 🙂 thanks for raising @visch