Ingest Functions

The Adp.Ingest class is used for loading data from different sources into tables and views.

The Autonomous Database Load Data API for Python allows the following operations:

Copy tables from the Database Link to tables or views of the current schema
Copy objects from the Cloud Storage Link to tables or external tables of the current schema
Create table with data from json

These operations create the corresponding Ingest Job

Database Link operations

Database Link functions provide the syntax and descriptions of the classes, methods, attributes, and parameters of the application programming interface to the Database Link.

Get Consumer Group Procedure

This function receives list of consumer groups to run your SQL or PL/SQL code.The values match the database services available when connecting to the database. This feature is available only if you have the EXECUTE privilege on the CS_SESSION package.

Syntax

Ingest.get_consumer_groups()

Example

In this example, you get the list of the consumer groups:

adp.Ingest.get_consumer_groups() //Output:['LOW', 'MEDIUM', 'HIGH']

Get Database Links Procedure

Return list of available database links (to create database link see Database LinksDatabase Links in "Oracle® Database Database Administrator’s Guide 21c").

Ingest.get_database_links(owner)

Parameters:

owner: This field displays the owner of Database Links. If this field is missing, the tool uses the current schema owner.

Example

In this example, you get the list of the Database Links:

adp.Ingest.get_database_links()
 
//Output
{"nodes":
[
    {"label":"***********_*********_SH.REGRESS.RDBMS.DEV.US.ORACLE.COM",
     "type":"DB_LINK",
     "id":"\"ADPTEST\".\"DB_LINK\".\"***********_*********_SH.REGRESS.RDBMS.DEV.US.ORACLE.COM\"",
    "data":{
        "name":"********_*******_SH.REGRESS.RDBMS.DEV.US.ORACLE.COM",
        "namespace":"DB_LINK",
        "path":"\"DB_LINK\".\"*************_********_SH.REGRESS.RDBMS.DEV.US.ORACLE.COM\"",
        "schema":"ADPTEST",
        "application":"DATABASE",
        "created":"2024-05-02T08:30:15Z",
        "updated":"2024-05-02T08:30:15Z"
        }
    }
],"links":[]}

Get Tables in the Database Link Procedure

Return list of tables and views from the database link.

Ingest.get_db_link_owner_tables(db_link)

Parameters:

db_link: This name of the Database Link.

Example

In this example, you get the list of the objects in a specified Database Link:

adp.Ingest.get_db_link_owner_tables("**************_********_SH.REGRESS.RDBMS.DEV.US.ORACLE.COM")
 
//Output
[
"APEX_DG_DATASET_ROWS": {
    "dbLink": "**************_********_SH.REGRESS.RDBMS.DEV.US.ORACLE.COM",
    "owner": "APEX_230200",
    "tableName": "APEX_DG_DATASET_ROWS",
    "numRows": 3,
    "avgRowLen": 54
  },...
]

Copy Table from Database Link Procedure

Create table from the specified tables into the current database.

Syntax

Ingest.copy_tables_from_db_link(tables, consumer_group)

Parameters:

tables: Array of tables description. Each description has 4 fields.
owner: This field displays the owner of the Database Link. If this field is missing, the tool uses the current schema owner.
table_name: the name of the table in the Database Link. This field is mandatory.
db_link: The name of the Database Link. This field is mandatory.
target_table_name: The name of the target table. If this field is missing, the tool uses the source table name.
consumer_group: the consumer group. Default value is "LOW".

Examples

In this example, you can create table based on the table in the Database Link:

adp.Ingest.copy_tables_from_db_link([{'owner':'SH', 'tableName':'PRODUCTS', 'dbLink':'PHOENIX119757_ORDS_SH.REGRESS.RDBMS.DEV.US.ORACLE.COM','targetTableName': 'PRODUCT'}], 'HIGH')
 
//Output
 
[
    {
    'schema': 'SH',
    'tableName': 'PRODUCTS',
    'targetTableName': 'PRODUCTS',
    'name': '***********_***********_SH.REGRESS.RDBMS.DEV.US.ORACLE.COM',
    'rowsCopied': 766
    }
]

The method returns the list of tables you create. The dictionary of the output is the same as in input parameters, and additional value is inserted row count.

Link Table from Database Link Procedure

Create view from the specified tables into the current database. Take into account that the result string has no number of rows.

Ingest.link_tables_from_db_link(tables, consumer_group)

Parameters:

tables: Array of tables description. Each description has 4 fields
- owner: This field displays the owner of the Database Link. If this field is missing, the tool uses the current schema owner.
- table_name: the name of the table in the Database Link. This field is mandatory.
- db_link: The name of the Database Link. This field is mandatory.
- target_table_name: The name of the target table. If this field is missing, the tool uses the source table name.
consumer_group: the consumer group. Default value is "LOW".

Example

In this example, you can create table based on the table in the Database Link:

adp.Ingest.link_tables_from_db_link([{'owner':'SH', 'tableName':'PROMOTIONS', 'dbLink':'**************_********_SH.REGRESS.RDBMS.DEV.US.ORACLE.COM', 'targetTableName': 'PROMOTIONS'}},'HIGH')
 
//Output
 
[
    {
    'schema': 'SH',
    'tableName': 'PROMOTIONS',
    'targetTableName': 'PROMOTIONS',
    'name': '**************_********_SH.REGRESS.RDBMS.DEV.US.ORACLE.COM'}
]

Cloud Storage Link operations

Cloud Storage Link functions provide the syntax and descriptions of the classes, methods, attributes, and parameters of the application programming interface to the Cloud Storage Link tool of Data Studio suite of tools.

These methods allows managing Credentials and Cloud Storage Link, and import cloud objects to tables or external tables of the current schema.

Get List of Credentials Procedure

Return list of available Credentials

Ingest.get_credential_list()

The above function returns a list of Credentials.

Example

In this example, you can get all Credentials:

adp.Ingest.get_credential_list()
 
{
  "items": [
    {
      "credential_name": "PROVIDER_DEMO_DELTA_SHARING_111$SHARE_CRED",
      "username": "BEARER_TOKEN",
      "windows_domain": null,
      "comments": "{\"comments\": \"Created via DBMS_CLOUD.create_credential\",\"user_comments\": \"\"}"
    },
    ...
    ]
}

Create Credential Procedure

Create Simple Credential. For Microsoft Azure cloud use is an account name, and password is an access key, for Amazon user is an access key ID, and password is secret access key, for Google user is HMAC access key, and password is HMAC access secret.

Syntax

Ingest.create_credential(credential_name, user, password)

Parameters:

credential_name: the name of a new credential
user and password depend on Cloud system: For Microsoft Azure cloud user is an account name, and password is an access key, for Amazon user is an access key ID, and password is secret access key, for Google user is HMAC access key, and password is HMAC access secret.

Example

In this example, you can create a Credential with specified user and password:

adp.Ingest.create_credential('TEST", 'ADMIN', 'PASSWORD')

Create OCID Credential Procedure

Create credentials using OCI Signing Keys.

Syntax

Ingest.create_ocid_credential(credential_name, user_ocid, tenancy_ocid,
                          private_key, fingerprint)

This function creates credentials using OCI Signing Keys.

Parameters:

credential_name: The name of the credential.
user_ocid: The OCID of the the user.
tenancy_ocid: The OCID of the tenancy.
private_key: The private key in the RSA key pair. The private key spans over multiple lines. Ensure to replace all the newline characters with the space character and use the resulting key.
fingerprint: The fingerprint for the RSA key pair that you're using to access OCI. See Create Credentials using REST API.

Example

adp.Ingest.create_ocid_credential('OCI_NATIVE_CRED', 'ocid1.user.oc1..aaaaaaaatfn77fe3fxux3o5lego7glqjejrzjsqsrs64f4jsjrhbsk5qzndq','ocid1.user.oc1..aaaaaaaatfn77fe3fxux3o5lego7glqjejrzjsqsrs64f4jsjrhbsk5qzndq', 'MIIEogIBAAKCAQEAsbNPOYEkxM5h0DF+qXmie6ddo95BhlSMSIxRRSO1JEMPeSta0C7WEg7g8SOSzhIroCkgOqDzkcyXnk4BlOdn5Wm/BYpdAtTXk0sln2DH...', '4f:0c:d6:b7:f2:43:3c:08:df:62:e3:b2:27:2e:3c:7a')

Drop Credential Procedure

Drop credential with the specified name.

Ingest.drop_credential(credential_name)

Parameters:

credential_name: The name of the credential.

Example

In this example, you can drop Credential with the name 'TEST':

adp.Ingest.drop_credential('TEST')

Get Cloud Storage Links List Procedure

Syntax

Ingest.get_cloud_storage_link_list(owner)

Parameters:

owner: This field displays the owner of the Database Link. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can the list of Cloud Storage Links :

Ingest.get_cloud_storage_link_list()
 
// Output
 
{
  "nodes": [
    {
      "label": "AA",
      "type": "CLOUD_STORAGE_LINK",
      "id": "\"ADMIN\".\"STORAGE_LINK\".\"AA\"",
      "data": {
        "name": "AA",
        "entityID": 28897,
        "namespace": "STORAGE_LINK",
        "path": "\"STORAGE_LINK\".\"AA\"",
        "schema": "ADMIN",
        "application": "CLOUD",
        "created": "2023-01-30T21:42:17Z",
        "updated": "2023-01-30T21:42:17Z",
        "catalog": "LOCAL"
      }
    },
    ...
    ]
}

Create Cloud Storage Link Procedure

Create Cloud Storage Link Procedure based on Cloud Storage URI and credentials. Credential may be skipped if storage link is a public bucket.

Syntax

Ingest.create_cloud_storage_link(storage_link_name, uri, credential_name,
                          description)

Parameters:

storage_link_name: The name for the cloud storage link. This field is mandatory.
uri: The URL Path (URI) except for the file name. This field is mandatory.
credential_name: The credential name must conform to Oracle object naming conventions.
description: a description for the link. If description is missing, the tool uses storage_link_name instead.

Example

In this example, you can create the Cloud Storage Link with the name 'TEST'

Ingest.create_cloud_storage_link('TEST', 'https://.../test-bucket/o/', None, 'OCI Storage Link')

Drop Cloud Storage Link Procedure

Drop Cloud Storage Link based on storage link name.

Ingest.drop_cloud_storage_link(storage_link_name)

Parameters:

storage_link_name: The name for the cloud storage link.

Example

In this example, you can drop Cloud Storage Link with the name 'TEST'

Ingest.drop_cloud_storage_link('TEST')

Get Cloud Storage Objects Procedure

Get the list of objects in Cloud Storage Link.

Syntax

Ingest.get_cloud_objects(storage_link,owner)

This function returns the list of objects in cloud storage link.

Parameters:

storage_link: The name for the cloud storage link.
owner: This field displays the owner of the Database Link. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can list of objects in Cloud Storage Link with the name 'TEST'

Ingest.get_cloud_objects('TEST')
 
// Output
{
"nodes": [
     {
      "label": "users/1623813236395.parquet",
      "type": "CLOUD_OBJECT",
      "id": "\"ADMIN\".\"STORAGE_LINK\".\"TEST\".\"OBJECT\".\"users/1623813236395.parquet\"",
      "data": {
        "name": "users/1623813236395.parquet",
        "namespace": "OBJECT",
        "path": "\"STORAGE_LINK\".\"TEST\".\"OBJECT\".\"users/1623813236395.parquet\"",
        "schema": "ADMIN",
        "annotation": {
          "bytes": 1835742,
          "checksum": "8c215516638037427850b03f0e111850",
          "isFolder": false,
          "fileName": "1623813236395.parquet",
          "uri": "https://.../test-bucket/o/users/1623813236395.parquet"
        },
        "application": "CLOUD",
        "created": "2022-08-02T14:21:26Z",
        "updated": "2022-08-02T14:21:26Z",
        "catalog": "LOCAL"
      }
    },...
    ]
}

This returns the list of available consumer groups.

Copy Cloud Objects Procedure

Copy cloud objects from the Cloud Storage Link to the tables in the current schema

Ingest.copy_cloud_objects(objects, consumer_group)

Parameters:

objects: Array of tables description. Each description has 4 fields.
- storageLink: The name of the Cloud Storage Link. This field is mandatory.
- objectName: the name of the file in the Cloud Storage Link. See name field in results of get_cloud_object method. This field is mandatory.
- targetTableName: The name of the target table. If this field is missing, the tool uses the filename as the table name.
consumer_group: the consumer group. Default value is "LOW".

Example

In this example, you can create table and load data from the object in Cloud Storage Link.

adp.Ingest.copy_cloud_objects([{'storageLink': 'TEST', 'objectName': 'users/testData.csv','targetTableName': 'TESTDATA'}, 'HIGH')
 
output:
[
  {
    "storageLink": "TEST",
    "targetTableName": "TESTDATA",
    "objectName": "users/testData.csv",
    "rowsCopied": 588
  }
]

Link Cloud Objects Procedure

Create external tables based on cloud objects from the Cloud Storage Link.

Ingest.link_cloud_objects(objects, consumer_group)

Parameters:

objects: Array of tables description. Each description has 4 fields.
- storageLink: The name of the Cloud Storage Link. This field is mandatory.
- objectName: the name of the file in the Cloud Storage Link. See name field in results of get_cloud_object method. This field is mandatory.
- targetTableName: The name of the target table. If this field is missing, the tool uses the filename as the table name.
consumer_group: the consumer group. Default value is "LOW".

Example

In this example, you can create external table from specified object in Cloud Storage Link.

adp.Ingest.link_cloud_objects([{'storageLink': 'TEST', 'objectName': 'users/testData.csv','targetTableName': 'TESTDATA'}, 'HIGH')
 
output:
[
  {
    "storageLink": "TEST",
    "targetTableName": "TESTDATA",
    "objectName": "users/testData.csv",
    "rowsCopied": 588
  }
]

Create Table from json Data Procedure

Ingest.load_data(tables)

Parameters:

tables is the list of dictionaries with the following fields:
- content: Is the content of data, it is a dictionary with key as column name and values as a list of values of the column.
- targetTableName: The name of table.

Example

In this example, you can create table and fill it with data from content:

content = {
"Year_id":[11,12,13,14,15],
"Year_name": ["CY2011","CY2012","CY2013","CY2014","CY2015"],
"Year_end_date": ["31-DEC-11","31-DEC-12","31-DEC-13","31-DEC-14","31-DEC-15"],
"Quarter_id":[211,212,213,214,215],
"Quarter_name":["Q2CY2011","Q2CY2012","Q2CY2013","Q2CY2014","Q2CY2015"],
"Quarter_end_date":["30-JUN-11","30-JUN-12","30-JUN-13","30-JUN-14","30-JUN-15"]
}
  
content_list=[{"content": content, "targetTableName": "TestLoad"}]
  
adp.Ingest.load_data(content_list)
  
  
//Output
  
[{'fileName': 'TESTLOAD', 'targetTableName': 'TESTLOAD', 'rowsCopied': 6}]