Skip to main content

Ingest data into CDF RAW

To ingest data to a table in CDF RAW, using the Postgres Gateway and an ETL tool, you need to define the table structure in the PostgreSQL gateway before setting up the sink/destination in your ETL tool.

Step 1: Manage tables in PostgreSQL gateway APIs

tip

The Cognite API will help create, retrieve, alter, and delete tables in the PostgreSQL gateway for a table in CDF RAW.

Create a table

  1. Sign in to Postman. You can also use the web version of Postman if you haven't downloaded the tool.

  2. In your workspace, create a new request and enter the request name Create table.

  3. Select the request type as POST and enter your request URL - https://skywarp.{cluster}.cognite.ai/user/{username}/tables. Replace {cluster} with your CDF cluster and {username} with the username credentials. See Setting up PostgreSQL for more information.

  4. On the Authorization tab, select OAuth 2.0 as the type of Authorization. To get your access token, see How to get your access token in Postman.

  5. The Body tab allows you to specify the data you need to send with a request for every POST request. Inside the Body tab, click raw as the data model and select JSON from the dropdown as the data format and enter the below JSON:

    {
    "items": [
    {
    "destination": "raw",
    "name": "movies",
    "columns": ["title", "year", "budget"],
    "options": {
    "primary_key": "title",
    "database": "my_database" /* my_database is your database name in CDF */,
    "table": "my_table" /* my_table is your table name in CDF */
    }
    }
    ]
    }
  6. Click Send to fetch a response.

Creating a table

You have successfully created a new table.

NOTE

When you've created a table in the PostgreSQL gateway, the corresponding table in CDF RAW is created when the first row is ingested using the PostgreSQL gateway.

List tables

  1. Create a new request and enter the request name List tables.

  2. Select the request type as GET and enter your request URL as

    https://skywarp.{cluster}.cognite.ai//user/{username}/tables.

  3. Select Authorization type as OAuth2.0.

  4. Click Send to fetch the response.

Listing custom table

You can view the list of all custom tables successfully. The id is a unique ID generated for your table.

Alter a table

  1. Create a new request and enter the request name Update table.

  2. Select the request type as POST and enter your request URL as

    https://skywarp.{cluster}.cognite.ai/user/{username}/tables/update.

  3. Select Authorization type as OAuth2.0.

  4. Inside the Body tab, click raw as the data mode, and select JSON as the data format.

  5. Enter this JSON:

    {
    "items": [
    {
    "id": "123" /* the unique id of your table */,
    "update": {
    "name": {
    "set": "new_name"
    },
    "columns": {
    "add": ["another_column"]
    }
    }
    }
    ]
    }
  6. Click Send to fetch the request response.

Your table is now updated with the new values.

NOTE

You can only add new columns to the table. You cannot delete existing columns or modify the data types.

Delete a table

  1. Create a new request and enter the request name Delete table.

  2. Select the request type as POST and enter your request URL as

    https://skywarp.{cluster}.cognite.ai/user/{username}/tables/delete.

  3. Select Authorization type as OAuth2.0.

  4. Inside the Body tab, click raw as the data model and select JSON as the data format.

  5. Enter this JSON:

    {
    "items": [{ "id": 123 }]
    }
  6. Click Send to fetch the response.

Deleting a custom table

{} in the response indicates you have successfully deleted the table.

tip

When you delete a table, it's also deleted in the PostgreSQL gateway, but not from CDF RAW.

Step 2: Complete the connection in ETL

  1. Verify that the table you created above is available as a foreign table in your ETL tool.

  2. Configure your ETL pipeline between the source system and CDF RAW.