DDL
建立Schema：
- Hive：
CREATE DATABASE test with DBPROPERTIES(
catalog='hive',
location = 'oss://brty-dataware/test/'
);
catalog：数据支持的类型，即DATABASE的类型。
location：连接信息，即数据的存储地址，根据catalog的类型改变。
- Mysql
CREATE SCHEMA mysql_db WITH DBPROPERTIES (
CATALOG = 'mysql',
LOCATION = 'jdbc:mysql://rm-2zer0vg58mfofake.mysql.rds.aliyuncs.com:3306/dla_test',
USER = 'dla_test',
PASSWORD = 'the-fake-password',
VPC_ID = 'vpc-2zeij924vxd303kwifake',
INSTANCE_ID = 'rm-2zer0vg58mfo5fake'
);
建立TABLE：
- 非分区表：
CREATE EXTERNAL TABLE `test`.`test_0430` (
`p_partkey` int,
`p_name` string,
`p_mfgr` string,
`rank` int
) COMMENT 'TPCH 1x customer in text format.'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS `TEXTFILE`
LOCATION 'oss://brty-dataware/test/test_0430/'
TBLPROPERTIES (
'auto.create.location'='true'
);
TBLPROPERTIES：设置表的属性，该样例的属性为，如果建表时location没有对应目录，则自动创建。
- 分区表：
CREATE EXTERNAL TABLE primitives_text_p (
id INT COMMENT 'default',
bool_col BOOLEAN COMMENT 'default',
tinyint_col TINYINT COMMENT 'default',
smallint_col SMALLINT COMMENT 'default',
string_col STRING COMMENT 'default',
timestamp_col TIMESTAMP COMMENT 'default'
)
PARTITIONED BY (
year INT COMMENT 'default',
month INT COMMENT 'default'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
ESCAPED BY '\\'
STORED AS TEXTFILE
LOCATION 'oss://your-bucket/path/to/primitives_text_p';