Updated on 2024-05-27 GMT+08:00

Querying Database Object Information

Function

This API is used to query database object information. Use the new API in Querying Database Object Information for query.

Constraints

  • This API supports only the synchronization scenarios from Oracle-> GaussDB distributed, from MySQL to Kafka, and from GaussDB(for MySQL) to Kafka.
  • This API can be used only in certain regions. For details, see Endpoints.

URI

GET /v5/{project_id}/jobs/{job_id}/db-objects

Table 1 Path parameters

Parameter

Mandatory

Type

Description

project_id

Yes

String

Project ID of a tenant in a region.

For details about how to obtain the project ID, see Obtaining a Project ID.

job_id

Yes

String

Task ID.

Table 2 Query parameters

Parameter

Mandatory

Type

Description

offset

No

Integer

Offset, which is the position where the query starts. The value must be greater than or equal to 0.

limit

No

Integer

Number of items displayed per page.

type

Yes

String

Type of the object information to be queried. Values:

  • source: Query information about objects in the source database.
  • modified: Query information about the selected objects that are not delivered or synchronized.
  • synchronized: Query information about synchronized (delivered) objects.

db_names

No

Array

A specified database.

Request Parameters

Table 3 Request header parameters

Parameter

Mandatory

Type

Description

Content-Type

Yes

String

MIME type of the request body. Use the default value application/json. For APIs used to upload objects or images, the value varies depending on the flow type.

The default value is application/json.

X-Auth-Token

Yes

String

User token obtained from IAM.

It is a response to the API for obtaining a user token. This API is the only one that does not require authentication.

After a request is processed, the value of X-Subject-Token in the header is the token value.

X-Language

No

String

Request language type.

Default value: en-us

Enumerated values:

  • en-us
  • zh-cn

Response Parameters

Status code: 200

Table 4 Response body parameters

Parameter

Type

Description

object_scope

String

Type of the database in real-time synchronization.

target_root_db

Object

Destination database information for database object migration or synchronization. This parameter is mandatory for database synchronization from Layer 2 to Layer 3.

For details, see Table 5.

object_info

Map<String,DatabaseObject>

Migration or synchronization object information.

For details, see Table 6.

max_table_num

Integer

Threshold of the number of tables in a database.

status

String

Status of submitting the query of database object information. This parameter is returned only when the API in Obtaining the Result of Querying Database Object Information is invoked.

Enumerated values:

  • success
  • failed
  • pending

id

String

Task ID.

Table 5 Data structure description of field target_root_db

Parameter

Type

Description

db_name

String

Database name.

db_encoding

String

Encoding format. The default value is UTF-8.

Table 6 Data structure description of field object_info

Parameter

Type

Description

sync_type

String

Type of the database in real-time synchronization. config: This parameter is mandatory only when the database is used as an association database in advanced settings for data filtering. In this case, the database and its schemas and tables will not be synchronized to the destination database. The name and all parameters do not take effect. Enter the associated objects in schemas and tables.

NOTE:

To synchronize the database-level object, set sync_type to config for the lower-level object.

Enumerated values:

  • config

name

String

Name of the database in the destination database (database name mapping).

all

Boolean

Whether to migrate or synchronize the entire database.

NOTE:
  • If data filtering, column filtering, and column mapping are required for schemas, tables, and columns in the database, set this parameter to false. Otherwise, set this parameter to true.
  • If additional columns are required for tables in the database, set this parameter to true and enter additional column information in columns of table-level objects.
  • If the table columns in the database are used as association columns in advanced settings for data filtering, set this parameter to true, enter the association column information in columns, and enter the configuration criteria in advanced settings for data filtering in config_conditions.

schemas

Map<String,SchemaObject>

Schema to be migrated or synchronized. This parameter is mandatory when all is set to false.

For details, see Table 7.

tables

Map<String,TableObject>

Table to be migrated or synchronized. This parameter is mandatory when all is set to false.

For details, see Table 8.

total_table_num

Integer

Number of tables in the database. If the number of tables exceeds the threshold, this parameter is not displayed.

is_synchronized

Boolean

Whether synchronization has been performed.

Table 7 Data structure description of field schemas

Parameter

Type

Description

sync_type

String

Type of the schema in real-time synchronization. Values:

config: This parameter is mandatory only when the schema is used as an association schema in advanced settings for data filtering. In this case, the schema and its tables will not be synchronized to the destination database. The name and all parameters do not take effect. Enter the associated objects in tables.

NOTE:

To synchronize the schema-level object, set sync_type to config for the lower-level object.

name

String

Name of the schema in the destination database (schema name mapping).

all

Boolean

Whether to migrate or synchronize the entire schema.

NOTE:
  • If data filtering, column filtering, and column mapping are required for tables and columns in the schema, set this parameter to false. Otherwise, set this parameter to true.
  • If additional columns are required for tables in the schema, set this parameter to true and enter additional column information in columns of table-level objects.
  • If the table columns in the schema are used as association columns in advanced settings for data filtering, set this parameter to true, enter the association column information in columns, and enter the configuration criteria in advanced settings for data filtering in config_conditions.

tables

Map<String,TableObject>

Table to be migrated or synchronized. This parameter is mandatory when all is set to false.

For details, see Table 8.

Table 8 Data structure description of field tables

Parameter

Type

Description

sync_type

String

Type of the table in real-time synchronization. Values:

config: This parameter is mandatory only when the table is used as an association table in advanced settings for data filtering. In this case, the table and its columns will not be synchronized to the destination database. The name, all, filtered, and filter_conditions parameters do not take effect. Enter the associated objects in columns and the configuration criteria in advanced settings for data filtering in config_conditions.

NOTE:

To synchronize the table-level object, set sync_type to config for the lower-level object.

type

String

Object type. Values:

  • table: indicates a table.
  • view: indicates a view.
  • procedure: indicates a stored procedure.

Enumerated values:

  • table
  • view
  • procedure

name

String

Name of the table in the destination database (table name mapping).

all

Boolean

Whether to migrate or synchronize the entire table.

NOTE:
  • If column filtering and column mapping are not required for the table, set this parameter to true. Otherwise, set this parameter to false.
  • If additional columns are required for the table, set this parameter to true and enter additional column information in columns.
  • If the table columns are used as association columns in advanced settings for data filtering, set this parameter to true, enter the association column information in columns, and enter the configuration criteria in advanced settings for data filtering in config_conditions.

db_alias_name

String

Mapping of database names at the table level in the one-to-many scenario.

schema_alias_name

String

Mapping of schema names at the table level in the one-to-many scenario.

filtered

Boolean

Whether to filter data in the table.

filter_conditions

Array of strings

Filtering criteria for the table data. The processing rule value is a SQL statement. The value contains a maximum of 512 characters.

Minimum length: 0

Maximum length: 512

config_conditions

Array of strings

Configuration criteria in advanced settings for data filtering of the table. This parameter is mandatory when the table is used as an association table for query. The processing rule value is a SQL statement. The value contains a maximum of 512 characters.

Minimum length: 0

Maximum length: 512

is_synchronized

Boolean

Whether synchronization has been performed.

columns

Map<String,ColumnObject>

Columns to be synchronized, mapped, filtered, or added. This parameter is mandatory when column filtering, column mapping, and additional columns are required. This parameter takes effect only in real-time synchronization tasks. This parameter is mandatory when all is set to false.

For details, see Table 9.

Table 9 Data structure description of field columns

Parameter

Type

Description

sync_type

String

Type of the column in real-time synchronization. Values:

config: This parameter is mandatory only when the column is used as an association column in advanced settings for data filtering. If the column is a primary key column or index column required for optimizing the query, primary_key_for_data_filtering or index_for_data_filtering is mandatory.

NOTE:

Whether to synchronize the column to the destination database depends on the filtered parameter, which is different from the database, schema, and table-level synchronization.

primary_key_for_data_filtering

String

Whether the column is a primary key column in advanced settings for data filtering. If the column is a primary key column, set this parameter to the column name. Otherwise, leave this parameter blank.

index_for_data_filtering

String

Whether the column is an index column required for optimizing the query. The index is added to the cached data. It does not affect the source table. If the column is an index column in advanced settings for data filtering, this parameter is mandatory. Otherwise, leave this parameter blank.

name

String

Name of the column in the destination database (column name mapping). If the column is an additional column, the value must be the same as the column name in the table-level object.

type

String

Data type of the column. Column filtering: Enter the data type of the source column. Additional column adding: Enter the data type of the new column. The values and constraints vary depending on the operation type. Values:

  • Default: The value can be int, long, varchar(256), datetime, or timestamp.
  • create_time: The value can be long, datetime, or timestamp.
  • update_time: The value can be long, datetime, or timestamp.
  • Expression: The value can be varchar (256), and the column value must be concat(__current_database, '@', __current_table).
  • serverName@database@table: The value can be varchar(256).

primary_key_for_column_filtering

String

Whether the column is a primary key column in column mapping. If the column is a primary key column, set this parameter to PRI. Otherwise, leave this parameter blank.

filtered

Boolean

Whether the column is filtered out. This parameter cannot be used together with the additional parameter. Values:

  • true: indicates that the column will be synchronized.
  • false: indicates that the column is filtered out and will not be synchronized.

additional

Boolean

Whether the column is an additional column. If the column is an additional column, the value of name must be the same as the column name in the table-level object and cannot be used together with the filtered parameter.

operation_type

String

Operation type. The new column is filled with a specific operation type. Values:

  • Default: "operation_type":"ADDITIONALCOLUMN,default_value"
  • create_time: "operation_type":"ADDITIONALCOLUMN,create_time"
  • update_time: "operation_type":"ADDITIONALCOLUMN,update_time"
  • Expression: "operation_type":"ADDITIONALCOLUMN,expression"
  • serverName@database@table: "operation_type":"ADDITIONALCOLUMN,server_database_table"

value

String

Value of the additional column. Note:

  • This parameter is mandatory only when operation type is set to Default or serverName@database@table.
  • When operation type is set to Expression, this parameter has a fixed value of concat(__current_database, '@', __current_table).

Status code: 400

Table 10 Response body parameters

Parameter

Type

Description

error_code

String

Error code.

Minimum length: 12

Maximum length: 12

error_msg

String

Error message.

Minimum length: 1

Maximum length: 512

Example Request

Querying information about the synchronized objects

https://{endpoint}/v5/054ba152d480d55b2f5dc0069e7ddef0/jobs/c7debc9c-8e09-4a5d-8dd6-cc44f78jb20r/db-objects?type=synchronized

Example Response

Status code: 200

OK

{
  "target_root_db" : {
    "db_name" : "db1",
    "db_encoding" : "UTF-8"
  },
  "object_scope" : "table",
  "object_info" : {
    "db1" : {
      "name" : "db1",
      "all" : false,
      "schemas" : {
        "schema1" : {
          "name" : "schema1",
          "all" : false,
          "tables" : {
            "table1" : {
              "name" : "table1",
              "type" : "table",
              "all" : true,
              "is_synchronized" : false
            }
          }
        }
      },
      "total_table_num" : 1
    }
  },
  "max_table_num" : 500
}

Status code: 400

Bad Request

{
  "error_code" : "DRS.10000010",
  "error_msg" : "Job does not exist, please check job ID."
}

Status Code

Status Code

Description

200

OK

400

Bad Request

Error Code

For details, see Error Code.