Migrating to Canvas Data 2 - Rework old code or use a new approach

[ChrisWolf93]

Hello everyone,

We are working to update to Canvas Data 2, but our current method of using canvas-data-sdk is no longer compatible following the canvas data 2 changes.

Our current method does the following with 3 commands total using canvas-data-sdk on canvas data 1:

1. Gets the schema for ALL tables
2. Generates a Postgres or Amazon Redshift compatible DDL script based on the JSON-formatted schema above.
3. Fetches all of the files for that dump, decompress them, and re-assembles them into a single file for each table

I have used the CLI and have been able to successfully get snapshots of data from a singular table, query a table, and so on. With that said, I am not sure the best way to recreate what was previously done using the tool linked above. 

Is trying to recreate the approach listed above with the CLI a good route to take? Or would it be best to rewrite the workflow in a different way, possibly just interacting with specific tables instead of all of the data?

Thanks!

[jerry_nguyen]

@ChrisWolf93 

There is a new CLI tool for Canvas Data 2 that you can use to download CD2: https://community.canvaslms.com/t5/Admin-Guide/How-do-I-use-the-Canvas-Data-2-Command-Line-Interface...

[ChrisWolf93]

Hi Jerry,

Thanks for the reply! I've updated my original post for clarity. 

I have used the CLI to pull and interact with data, but I'm unsure of the best way to replicate what we were using the canvas-data-sdk for in CD1. Would the current method listed in the original post still be a good approach using the new CLI? Or would a new approach likely be better? So far with the new CLI I have been struggling to interact with multiple tables at once.

[LeventeHunyadi]

I would recommend reviewing (and possibly revising) the existing integration in light of the extended feature set available in CD 2 (compared to CD 1):

• CD 2 (a.k.a. DAP) allows you to retrieve data incrementally (i.e. only fetch changes that took place since the last time you queried the table). DAP client library stores the last time a table was synchronized in a special database table called dap_meta. This allows you to chain incremental queries.
• You should only trigger a snapshot query once for every table. Use incremental queries ever after. (CD 1 only had snapshot queries.)
• Unlike CD 1, CD 2 has namespaces. Today, three namespaces exist: canvas, canvas_logs and catalog.
• DAP client library allows you to retrieve all tables in a namespace with the list command.
• CD 2 (a.k.a. DAP) API lets you fetch data in the Parquet format. You can directly insert Parquet data into Redshift with the COPY command. You may need a temporary table to stage data, remove records in the target table that exist in the temporary stage table, and finally insert all records in the temporary stage table to the target table. (AWS Redshift does not support INSERT or UPDATE.)