All Products
Search
Document Center

Create an OSS schema and a table

Last Updated: Jun 17, 2020

You can create an Object Storage Service (OSS) schema and an OSS table in Data Lake Analytics (DLA) to read data from files in OSS.

Create an OSS schema

  1. Log on to the DLA console.

  2. In the left-side navigation pane, click Endpoint. In the endpoint list, find the target endpoint and click Log on in DMS. On the DMS for Data Lake Analytics page, execute the following statement to create an OSS schema in DLA:

    1. CREATE SCHEMA oss_data_schema with DBPROPERTIES(
    2. catalog='oss',
    3. location = 'oss://oss_bucket_name/table/'
    4. );
    • catalog='oss': creates an OSS schema.

    • location: specifies the directory of the file in the OSS bucket, which must end with a slash (/).

      When you create an OSS table later, you must specify the location parameter the same as this OSS directory.

Create an OSS table

In this example, the schemas of dla_table_1 and dla_table_2 are the same as those of the shipping table and the order_table table in AnalyticDB for MySQL, respectively. In addition, dla_table_1 and dla_table_2 files have been uploaded to the table object in the OSS bucket.

  1. CREATE EXTERNAL TABLE IF NOT EXISTS dla_table_1 (
  2. id bigint NOT NULL COMMENT '',
  3. origin_state varchar NOT NULL COMMENT '',
  4. origin_zip varchar NOT NULL COMMENT '',
  5. destination_state varchar NOT NULL COMMENT '',
  6. destination_zip varchar NOT NULL COMMENT '',
  7. package_weight int NOT NULL COMMENT ''
  8. )
  9. ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
  10. STORED AS TEXTFILE
  11. LOCATION 'oss://oss_bucket_name/table/';
  1. CREATE EXTERNAL TABLE IF NOT EXISTS dla_table_2 (
  2. customer_id bigint NOT NULL COMMENT '',
  3. order_id varchar NOT NULL COMMENT '',
  4. order_time date NOT NULL COMMENT '',
  5. order_amount double NOT NULL COMMENT '',
  6. order_type varchar NOT NULL COMMENT '',
  7. address varchar NOT NULL COMMENT '',
  8. city varchar NOT NULL COMMENT '',
  9. order_season bigint COMMENT '',
  10. PRIMARY KEY (customer_id)
  11. )
  12. ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
  13. STORED AS TEXTFILE
  14. LOCATION 'oss://oss_bucket_name/table/';

What to do next

Create an AnalyticDB for MySQL schema