CD2 Complete Newbie Doc?

[asergay]

Hello Friends-

I am not a coder or dba. The existing CD2 documentation gets me to the point where I can run the cli on my Mac and get a list of tables, and then it stops. The Postman doc gets me making api calls, but not downloading any data.

I set up a postgres database, and I want to know how to hook up CD2 to it. If someone would like to help me, I'd be happy to write the instructions for others (I'm a technical writer). It seems unlikely that I'm the only Canvas admin without experience with this.

[ColinMurtaugh]

Hi - 

You may have already seen this, but there's a bunch of documentation on the cli tool here: https://pypi.org/project/instructure-dap-client/. 

Now that you're able to list the tables using the cli, I'd suggest using the 'initdb' and 'syncdb' commands to load/update tables in your postgres database. These high-level commands take care of all of the details of running queries, downloading data files, assembling them into SQL scripts, and updating your database. 

First, set the DAP_CONNECTION_STRING environment variable to your db connection string, something like:

export DAP_CONNECTION_STRING=postgresql://scott:password@localhost/testdb

Then run the initdb command for one of your tables to pull a full snapshot and load it into your database:

dap initdb --namespace canvas --table accounts

Now in your postgres database you should see two new tables: accounts and dap_meta. The dap_meta table is used by the cli to keep track of what tables have been fetched and when.  You should see a record in the dap_meta table with the details for your accounts table. 

Now that you have the initial snapshot of your accounts table stored locally, you can refresh it using the syncdb command: 

dap syncdb --namespace canvas --table accounts

The cli tool will take care of running the incremental query, fetching the data files, and updating your database. You can run syncdb on whatever schedule you need (maybe hourly, or daily) to keep the data fresh. 

You'll need to run the initdb/syncdb commands for each of the tables that you want to mirror locally. 

Hopefully this helps you get started! 

--Colin

[candace_girard]

Hello,

re: CD2 Complete Newbie

Same!

re: The Postman doc gets me making api calls, but not downloading any data.

Same!

Is there any additional documentation, or could someone provide a couple of basic examples of how to pull data in Postman without a postgres database? 

The Instructure CD2 collection includes a sample ‘get schema of table’

      {{BASE_URL}}/dap/query/canvas/table/accounts/schema

but I am unsure about how I can tweak this to pull all of the data rows for the accounts table, and also how to pull the rows for the accounts table with an ID = 1? If someone could help with these two basic things, I should be able to take it from there.

Thanks!

[LeventeHunyadi · Accepted solution]

I would strongly recommend that you check out the official Python client library. It takes most of the work off your shoulder.

In order to retrieve data by interacting with DAP API endpoints directly, you must first start a query job by invoking the API endpoint

https://api-gateway.instructure.com/dap/query/{namespace}/table/{table}/data

This is an asynchronous call; it will trigger the job but the job will take time to process. You can monitor the status by invoking the status endpoint. Once the job is complete, it returns a series of object IDs. You will need to exchange these object IDs to pre-signed URLs with URL exchange endpoint. Finally, you need to download the files that the pre-signed URLs point to with plain HTTP file download.

DAP API returns full table dumps for a single root account. There is no API for passing row-level filters.

If you think the process is complicated, I agree. This is why we have created the DAP client library, which has the commands snapshot and incremental to do all these steps for you.

[bruno_guimaraes]

Hi, 

I use PostgreSQL and the command syncdb not work for me. Returns the following error below:

INFO:pysqlsync.postgres:PostgreSQL version 16.0.2 final
2024-03-06 15:48:52,993 - ERROR - table not initialized, use `initdb`

Has anyone seen this error and know how to resolve it?

Thanks

[kyle_cole]

Hey @bruno_guimaraes 

Have you run an initial initdb? Syncdb needs the table to be created to insert new records.

[bruno_guimaraes]

Hey, Kyle_cole,

yes, i do this.

[kyle_cole]

Can you see the tables in Postgres? 

[bruno_guimaraes]

Yes, tables are created and some records are included with the initdb command, but when I use syncdb I receive the message I sent above.

[bruno_guimaraes]

+ CategoryInfo : NotSpecified: (INFO:pysqlsync..../db_canvasdata2:String
) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError

INFO:pysqlsync.postgres:PostgreSQL version 16.0.2 final
INFO:pysqlsync.postgres:connecting to postgres@localhost/db_canvasdata2
INFO:pysqlsync.postgres:PostgreSQL version 16.0.2 final
2024-03-06 18:38:59,793 - ERROR - table not initialized, use `initdb