Example for table_ddl option usage?

[mzapukhlyak]

Could you give an example of the table_ddl option? I haven't found the case in the documentation yet.

I have one highly deep and very large json file in multiple chunks, and it appears that sling is able to import the single chunk correctly, building a nice table in the PostgreSQL DB. Some field types still need to be specified more precisely/differently, so I decided to do it for the entire table using DDL file.

Can ddl be provided as a sql file, or which format of the table_ddl is expected here?

[flarco]

Sure, however, let me first ask, have you tried to ingest the whole folder of JSON files instead of just one by one? Sling should be able to iterate over all of them and attempt to add columns as they are discovered (assuming you are flattening with source option flatten: true). For the types, it's a tricky thing, so there is the target option adjust_column_type, which defaults to false. Sling will attempt to adjust the column type as values are discovered. Can you try turning it on?

Feel free to share your config and/or JSON structure sample.

[flarco]

For the table_ddl, here is a replication YAML example.

source: S3
target: PG

streams:
  's3://bucket/path/to/json_folder':
    source_options:
      flatten: true
    target_options:
      table_ddl: |
         create or replace table "my_schema"."my_table" ( 
         .... 
         )
    object: '"my_schema"."my_table"' # make sure to fully qualify so matches in DDL
    mode: full-refresh

[mzapukhlyak]

The idea of ingesting the entire directory with json chunks works perfectly. Thanks!

The flattening and import of 500 json files (approx 2 Gb size) took roughly 10 minutes. That is indeed quick. But if I'm not mistaken, the 30-40% of the time were spent for migrating columns from the temp table to the final table in the PostgreSQL DB. I know, it is not correct in terms of operations of persistent storage, but is it possible to prevent creation of the temp table and spare that time? There are another tools and procedures to verify the imported table in our workflow.

Ok, still need to QC the imported table, and compare with other methods, but I'm thinking about including sling package into the data import pipeline. So, my next step will be to try out the package for importing some 20-200 GB tables, and if that works, a switch to prevent creation of temp tables to save that time would be helpful.

[flarco]

Interesting suggestion. I guess if it's not a full-refresh or truncate task, we could skip the temp table if specified.

The temp table is a must in those cases (full-refresh or truncate mode) due to the ability of roll-back if something goes wrong, or if the process is interrupted, for example. If it were to truncate or drop the final table, there would be no way to undo it. Also, there are some QC operations that occur on the temp table, right before inserting into final, to ensure that the data is correct, like row count and checksum (only on smaller tables). When inserting into the final table, if that table already has records, the row counts would not match.

That would also not work for incremental mode, where there is a primary key specified, since a MERGE / UPDATE is needed. You'd need the temp table for that.

However, that could work in snapshot mode, or incremental mode with only update_key (no primary_key), since those are just straight inserts... If the process is interrupted or if there are errors for some reason, it could just roll-back the transaction. Sling still would not be able to do the basic row count QC, but that is an acceptable circumstance.

I'll open a branch for this. 👍

[flarco]

Opened #35