Ingest data into CDF RAW

To ingest data to a table in CDF RAW, using the PostgreSQL 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.

NOTE

This documentation requires that you have completed Step 3.

Step 1: Manage tables in PostgreSQL gateway APIs

consejo

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://{cluster}.cognitedata.com/api/v1/projects/{project}/postgresgateway/tables/{username}. See Setting up PostgreSQL for more information.

WHERE

• cluster is where your CDF instance exists. If you don't know the cluster name, contact Cognite support.

• project is the organization name of your CDF instance.

• username with the username credentials

1. 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.

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

    {
        "items": [
            {
                "type": "raw_rows",
                "tablename": "tablename",
                "options": {
                    "database": "my_database" /* my_database is your database name in CDF */
                    "table": "my_table" /* my_table is your table name in CDF */
                    "primaryKey": "title"
                },
                "columns": {
                    "title": {"type": "TEXT"},
                    "year": {"type":"TIMESTAMPTZ"}
                }
            }
        ]
    }

nota

tablename cannot be any of these built-in table names below:

• assets
• double_datapoints
• events
• extraction_pipeline_runs
• extraction_pipelines
• files
• labels
• raw_rows
• raw_tables
• relationships
• sequence_rows
• sequences
• string_datapoints
• time_series
• data_models
• views
• containers
• units
• unit_systems

3. Select Send to fetch a response.

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://{cluster}.cognitedata.com/api/v1/projects/{project}/postgresgateway/tables/{username}.

3. Select Authorization type as OAuth2.0.

4. Select Send to fetch the response.

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://{cluster}.cognitedata.com/api/v1/projects/{project}/postgresgateway/tables/{username}/delete.

3. Select Authorization type as OAuth2.0.

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

5. Enter this JSON:

   {
     "items": [{ "tablename": "<TABLENAME>" }]
   }

CAUTION

Only custom tables created with the examples above can be deleted.

1. Select Send to fetch the response.

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

consejo

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.