Delving into the Data Access Platform CLI & Client Library

[kyle_cole]

Overview:

Introducing the GA release of the Data Access Platform CLI & Client Library! In this post, we'll walk you through everything you need to know to effectively utilize the Data Access Platform (DAP) CLI. From prerequisites to installation instructions, environment variable setup, and essential commands for seamless database interaction, this guide has got you covered. If you're new to Canvas Data, this blog post is a must-read before diving in.

We will also use Postgres and MYSQL as the example databases in this post because they are supported by the CLI tool.

Prerequisites:

Before using the CLI, ensure you have the following prerequisites in place:

• Canvas Data Credentials
• Python version 3.9, 3.10, 3.11, or 3.12
• Database:
  • Postgres and MYSQL are the supported dialects

Installation:

Once you have everything ready to get started, let's proceed with installing the CLI. You will need to run one of the following three commands to install the CLI, depending on your database selection:

For Postgresql

pip install 'instructure-dap-client[postgresql]'

For MYSQL:

pip install 'instructure-dap-client[mysql]'

For everything else:

pip install instructure-dap-client

Environment Variables:

Setting your environment variables is beneficial because it eliminates the need to repeatedly type the variables every time you want to invoke a command, making the command much more efficient and manageable. We will be setting the following variables:

• API Url
• Client ID
• Client Secret
• Database Connection

In UNIX environments, you'll use the 'export' command. Below is an example of how you would enter the commands in your terminal or command prompt. For detailed instructions on setting the connection string, please follow this link.

export DAP_API_URL=https://api-gateway.instructure.com
export DAP_CLIENT_ID=us-east-1#0c59cade-...-2ac120002
export DAP_CLIENT_SECRET=xdEC0lI...4X4QBOhM
export DAP_CONNECTION_STRING=postgresql://kyle:password@server.example.com/testdb

Using the CLI:

Now that we're set up, let's begin using the CLI. We'll be employing two commands: 'initdb' and 'syncdb'.

initdb: This command automatically retrieves the schema and data from the DAP Query API, establishes a connection to your database, generates the required table based on the published schema, and inserts the downloaded data into the created table. Here's an example command:

dap initdb --namespace canvas --table accounts

Breaking down this command:

• Dap: Calling the CLI.
• Initdb: Telling the CLI to perform creation of the table and inserting the table.
• Namspace: designates the namespace to use.
• Table: What table to download and insert.

*If the table is already present in your database it will throw an error.

Syncdb: This command is utilized to update the database table after 'initdb' has been executed. It performs an incremental query, fetching only the changes since the last 'initdb' or previous 'syncdb'. It tracks the timing of the last synchronization using the 'dap_meta' table. It's crucial not to drop or alter this table to ensure proper functionality. Here's an example command:

dap syncdb --namespace canvas --table accounts

As you can see with this command it uses the same parameters as initdb.

Wrapping up:

Hopefully, this blog post helps jumpstart your use of the DAP CLI. If you're seeking a deeper dive or need more information, please check out the official documentation here and the pypi page.

[anna_lamb]

Thanks for sharing the info.  I finished the steps you listed above in my Windows OS.

 But when I tried to run the CLI command, I got an error say 'dap' is not recognized as an internal or external command' in CMD window.  Not sure what I missed.