@Edgar Ramírez (Arch.dev) Could you please guide me on how to flatten the

JSON

data from the

MySQL

tap configuration before it’s loaded into

BigQuery

? I’ve already set

flattening_enabled: true

in the

BigQuery

target configuration, but I’m not achieving the desired result. Here's my current `meltano.yml`:

plugins:
  extractors:
  - name: tap-mysql
    variant: transferwise
    pip_url: 
      git+<https://github.com/transferwise/pipelinewise.git#subdirectory=singer-connectors/tap-mysql>
    select:
    - '*.*'      # Select all tables first
    - '!*_audit*'      # Then exclude audit tables
    metadata:
      '*.*':    # Apply metadata to all non-excluded tables
        replication-method: INCREMENTAL
        replication_key: update_time
        key_properties:
        - id

  mappers:
  - name: meltano-map-transformer
    variant: meltano
    pip_url: git+<https://github.com/MeltanoLabs/meltano-map-transform.git>
    executable: meltano-map-transform
    mappings:
    - name: mysql_to_bq
      config:
        flattening_enabled: true
        flattening_max_depth: 3   # flatten only top-level properties
        stream_maps:
          "*":
            __alias: "{{ stream_name | regex_replace('^smartterm_', '') }}"

  loaders:
  - name: target-bigquery
    variant: z3z1ma
    pip_url: git+<https://github.com/z3z1ma/target-bigquery.git>
    config:
      batch_size: 10000
      flattening_enabled: true
      flattening_max_depth: 3
      partition_granularity: day
      dedupe_before_upsert: true
      timeout: 3600
      upsert: true

You probably need to make sure the type of the field emitted by tap-mysql can be flattened. If you run

meltano invoke tap-mysql --discover > catalog.json

and you inspect the contents of the file for the type of the

JSON

field, it should be

object

.

Yes, it is JSON object and below is a snippet the json file:

{
  "streams": [
    {
      "tap_stream_id": "smartterm-account_medical_history",
      "table_name": "account_medical_history",
      "schema": {
        "properties": {
          "id": {
            "inclusion": "automatic",
            "maxLength": 45,
            "type": [
              "null",
              "string"
            ]
          },
          "account_id": {
            "inclusion": "available",
            "maxLength": 45,
            "type": [
              "null",
              "string"
            ]
          },
          // More fields...
        },
        "type": "object"
      }
    }
  ]
}

Which properties are you expecting to be flattened? You are only showing two above and they are both

string

, so they won't be flattened (as Edgar said, only

object

can be flattened).

The JSON output is large that's I shared a snippet. It means I will have to flatten the tables when performing the transformation.

Still not sure I follow. What happens when you load without flattening? What exactly are you expecting to be loaded? From the above, I'd expect a BigQuery table with at least two columns:

id

and

account_id

.

@Reuben (Matatika) When the schema is loaded into BigQuery without being flattened, it uses the

stream_id

as the table name. However, what I need is to load the exact

table_name

that is returned. I believe achieving this would require flattening the

stream

.

Don't think that is anything to do with flattening. If you want the exact same table name from MySQL mirrored in BigQuery, then you need to manipulate the stream ID using

stream_maps

. It looks like you are trying to do something like that here

stream_maps:
  "*":
    __alias: "{{ stream_name | regex_replace('^smartterm_', '') }}"

but the correct key is

__alias__

. Also, that expression syntax is incorrect - it should be Python:

stream_maps:
  "*":
    __alias__: '__stream_name__.replace("smartterm_", "")'

https://sdk.meltano.com/en/latest/stream_maps.html#constructing-expressions Curious where you got that expression syntax from?

Thanks @Reuben (Matatika) that has been resolved. However, I'm being face with another error and it's the extractor

tap-mysql

throwback this error:

2025-01-27T18:15:47.090749Z [error    ] Extractor failed              
2025-01-27T18:15:47.091211Z [error    ] Block run completed.           block_type=ExtractLoadBlocks err=RunnerError('Extractor failed') exit_codes={<PluginType.EXTRACTORS: 'extractors'>: 1} set_number=0 success=False

Run invocation could not be completed as block failed: Extractor failed

Nothing else in the error trace? You can always isolate the tap process to debug:

meltano invoke tap-mysql

It return the stream and the nested schemas as well as the tables

Don't think so. Do you see an error trace in the output?

I'm not quite sure how else to explain it... I will try and troubleshoot in the meantime. Thanks for your assistance so far.

That particular target-bigquery has a number of ways of dealing with duplicates, so I'll just link to the readme here 😅 https://github.com/z3z1ma/target-bigquery?tab=readme-ov-file#upsert-aka-merge