SQL和DDL相关--客户数据平台-火山引擎

文档中心

导航

SQL和DDL相关

最近更新时间：2023.10.08 10:43:09首次发布时间：2023.10.08 10:43:09

1. 解析SQL中依赖的表和列

接口说明

本接口可以获取给定SQL中依赖的数据表和列。

请求地址

POST https://{domain}/aeolus/prep/userOpenAPI/v1/sql

请求参数

参数名称	类型	默认值	必填	说明
engineType	string		是
sql	string		是

请求参数示例

{
    "engineType": "hive",
    "sql": "select a, aa, cd from aaa.aaaa where aa='abc'"
}

返回结果示例

{
    "code": "prep/ok",
    "message": "成功",
    "extraMsg": null,
    "advice": null,
    "data": [
        {
            "dbName": "aaa",
            "tableName": "aaaa",
            "columns": [
                {
                    "name": "cd",
                    "keys": []
                },
                {
                    "name": "adc",
                    "keys": []
                },
                {
                    "name": "aa",
                    "keys": []
                },
                {
                    "name": "aa",
                    "keys": []
                }
            ]
        }
    ]
}

2. 获取SQL中的任务依赖

接口说明

本接口可以获取Prep中不同数据源的数据类型列表。

请求地址

POST https://{domain}/aeolus/prep/userOpenAPI/v1/sql/dependencies

请求参数

参数名称	类型	默认值	必填	说明
engineType	string		是
sql	string		是

请求参数示例

{
    "engineType": "hive",
    "sql": "select app_id from dw_aeolus.tb_458f78fe05eccdb819a34086a73dbec8 where date = '${date}'"
}

返回结果示例

{
    "code": "prep/ok",
    "message": "成功",
    "extraMsg": null,
    "advice": null,
    "data": [
        {
            "dbName": null,
            "tableName": null,
            "dataType": "hive",
            "subTasks": [
                {
                    "id": 105976995,
                    "nodeKey": null,
                    "nodeName": null,
                    "taskId": null,
                    "taskName": "标签测试_load_label_1654067098972_21f7",
                    "source": "raw_data",
                    "frequency": null,
                    "scheduleConf": {
                        "scheduleType": 1,
                        "scheduleTimeType": 0,
                        "scheduleFrequency": "daily",
                        "scheduleTimeSettings": [],
                        "interval": null,
                        "intervalUnit": 0
                    },
                    "offsetInfo": {
                        "offsets": [
                            0
                        ],
                        "offsetType": "set",
                        "offsetFrequency": "daily"
                    },
                    "mtime": null,
                    "dependencyStatus": 1,
                    "dependencyType": 1,
                    "recommendTaskInfo": {
                        "projectId": 756,
                        "databaseName": null,
                        "tableName": null,
                        "frequency": "daily",
                        "path": null,
                        "type": "shell",
                        "taskPartitions": []
                    }
                }
            ],
            "inputNodes": null,
            "readPartitionInfo": {
                "logic": true,
                "type": null,
                "operator": null,
                "operand": null,
                "children": []
            }
        }
    ]
}

3. 创建数据表

接口说明

本接口可以根据传入的信息创建对应的。

请求地址

POST https://{domain}/aeolus/prep/userOpenAPI/v1/ddl/createTable

请求参数

参数名称	类型	默认值	必填	说明
sourceTable	object		是	示例：{ "clusterName": "rangers", "dataSourceType": "click_house", "tableName": "tb_038bbfcabd5ba614bf9ebb129a575b7b", "dbName": "aeolus_data_db_rangers_202206", "partitions": [ "p_date" ] }
sourceRegisterInfo	object		是	示例：{ "partitionSourceType": "fromSource", "ownerEmailPrefix": "admin", "baseIdName": "base_id", "appId": 1, "partitionName": "p_date", "tableType": "click_house", "columns": [ { "type": "Nullable(Int64)", "name": "uid2" }, { "type": "Nullable(Int64)", "name": "uid3" }, { "type": "Nullable(String)", "name": "open_id" }, { "type": "Date", "name": "p_date" }, { "type": "Nullable(String)", "name": "app_id" }, { "type": "Nullable(Int64)", "name": "phone" }, { "type": "Int64", "name": "row_id" }, { "type": "Nullable(Int64)", "name": "uid" } ] }

请求参数示例

{
    "sourceRegisterInfo": {
        "partitionSourceType": "fromSource",
        "ownerEmailPrefix": "admin",
        "baseIdName": "base_id",
        "appId": 1,
        "partitionName": "p_date",
        "tableType": "click_house",
        "columns": [
            {
                "type": "Nullable(Int64)",
                "name": "uid2"
            },
            {
                "type": "Nullable(Int64)",
                "name": "uid3"
            },
            {
                "type": "Nullable(String)",
                "name": "open_id"
            },
            {
                "type": "Date",
                "name": "p_date"
            },
            {
                "type": "Nullable(String)",
                "name": "app_id"
            },
            {
                "type": "Nullable(Int64)",
                "name": "phone"
            },
            {
                "type": "Int64",
                "name": "row_id"
            },
            {
                "type": "Nullable(Int64)",
                "name": "uid"
            }
        ]
    },
    "sourceTable": {
        "clusterName": "rangers",
        "dataSourceType": "click_house",
        "tableName": "tb_038bbfcabd5ba614bf9ebb129a575b7b",
        "dbName": "aeolus_data_db_rangers_202206",
        "partitions": [
            "p_date"
        ]
    }
}

返回结果示例

{
    "code": "prep/ok",
    "message": "成功",
    "extraMsg": null,
    "advice": null,
    "data": {
        "tableSchema": {
            "columnSchema": [
                {
                    "name": "uid2",
                    "type": "long",
                    "isEngineType": false,
                    "engineType": "Int64"
                },
                {
                    "name": "uid3",
                    "type": "long",
                    "isEngineType": false,
                    "engineType": "Int64"
                },
                {
                    "name": "open_id",
                    "type": "string",
                    "isEngineType": false,
                    "engineType": "String"
                },
                {
                    "name": "p_date",
                    "type": "date",
                    "isEngineType": false,
                    "engineType": "Date"
                },
                {
                    "name": "app_id",
                    "type": "string",
                    "isEngineType": false,
                    "engineType": "String"
                },
                {
                    "name": "phone",
                    "type": "long",
                    "isEngineType": false,
                    "engineType": "Int64"
                },
                {
                    "name": "row_id",
                    "type": "long",
                    "isEngineType": false,
                    "engineType": "Int64"
                },
                {
                    "name": "uid",
                    "type": "long",
                    "isEngineType": false,
                    "engineType": "Int64"
                },
                {
                    "name": "base_id",
                    "type": "long",
                    "isEngineType": false,
                    "engineType": "Int64"
                }
            ],
            "primaryKeyList": [
                "base_id"
            ],
            "shardKey": "base_id",
            "sampleKey": "base_id",
            "ttl": 0,
            "partitionKeyList": [
                {
                    "name": "p_date",
                    "exprName": "p_date",
                    "pattern": "${DATE}",
                    "values": []
                }
            ],
            "kafkaTopic": null,
            "kafkaCluster": null,
            "groupId": null,
            "dataSourceId": null,
            "hiveTableParams": null
        },
        "clusterName": "cdp_to_test",
        "dbName": "cdp_internal",
        "tableName": "tb_038bbfcabd5ba614b__prep",
        "region": "cn",
        "dataSetTaskType": null,
        "baseIdColumn": null
    }
}

4. 创建数据表-v2

POST https://{domain}/aeolus/prep/userOpenAPI/v1/table/ddl/prepTable

请求参数

{
  "tableType": "clickhouse", // 表类型，hive/clickhouse
  "appId": 1, // 项目ID
  "ttl": 3, // TTL，单位天，默认0永久
  "clusterName": "public_cn", // 集群名
  "dbName": "aeolus_data_db_public_cn_202006", // 库名
  "tableName": "jk_test_tab221121_ck", // 表名
  "region": "cn", // 区域
  "tableSchema": {
    "primaryKeyList": [
      "p_date"
    ],
    "columnSchema": [
      {
        "type": "string",
        "name": "open_id"
      },
      {
        "type": "date",
        "name": "p_date"
      },
      {
        "type": "long",
        "name": "uid"
      }
    ], // 数据列信息
    "shardKey": "uid",
    "sampleKey": "uid",
    "partitionKeyList": [
      {
        "name": "p_date"
      }
    ] // 分区字段
  }
}

5. 恢复表分区

POST https://{domain}/aeolus/prep/userOpenAPI/v1/table/ddl/recoverPartitions

请求参数

{
    "tableType":"hive",
    "dbName":"dw_aeolus",
    "tableName":"asset_car_tob_pudongyongda_bhv"
}

6. 添加表分区

POST https://{domain}/aeolus/prep/userOpenAPI/v1/table/ddl/tablePartition

参数名称	类型	默认值	必填	说明
tableType	string	hive	是	默认填hive
dbName	string		是	数据库名
tableName	string		是	表名
location			是	分区存储的路径，可以是s3、hdfs、oss
partitionSpec	json		是	用于分区字段和分区字段值的描述

请求参数

{
    "tableType":"hive",
    "dbName":"dw_aeolus",
    "tableName":"tb_addd3ca12278f116dafc231ac39276fd",
    "location":"/user/hive/warehouse/dw_aeolus.db/tb_addd3ca12278f116dafc231ac39276fd/date=20201022",
    "partitionSpec":[{"partitionCol":"date","partitionColValue":"20201022"}]
}

7. 删除表分区

DELETE https://{domain}/aeolus/prep/userOpenAPI/v1/table/ddl/tablePartition

参数名称	类型	默认值	必填	说明
tableType	string	hive	是	默认填hive
dbName	string		是	数据库名
tableName	string		是	表名
partitionSpec	json		是	用于分区字段和分区字段值的描述

请求参数

{
    "tableType":"hive",
    "dbName":"dw_aeolus",
    "tableName":"tb_addd3ca12278f116dafc231ac39276fd",
    "partitionSpec":[{"partitionCol":"date","partitionColValue":"20201022"}]
}

8. 设置表TTL

POST https://{domain}/aeolus/prep/userOpenAPI/v1/table/ddl/tableTTL

参数名称	类型	默认值	必填	说明
tableType	string	hive	是	默认填hive
dbName	string		是	数据库名
tableName	string		是	表名
ttl	int		是	表ttl
ownerEmailPrefix	string	admin	是	用户名

请求参数

{
    "tableType":"hive",
    "dbName":"dw_aeolus",
    "tableName":"test_table_ttl",
    "ttl":2,
    "ownerEmailPrefix":"admin"
}