All Products
Search
Document Center

Wizard for creating OSS tables

Last Updated: Dec 16, 2019

Step 1: Activate an OSS data source

  1. Log on to the Data Lake Analytics console.

  2. In the left-side navigation pane, click Schema, and then click New Schema.

  3. On the New Schema page, locate the target OSS instance, and click Create by Wizard in Object Storage Service. If you have not activated any OSS data sources for your account, activate one as prompted.

Step 2: Create OSS Schemas

DLA allows you to create an OSS schema by following the wizard or by manually compiling an SQL statement. This topic describes how to create an OSS schema by following the wizard.

To compile an SQL statement for creating an OSS schema, see Compile SQL statements to create an OSS schema.

  1. Log on to the Data Lake Analytics console.

  2. In the left-side navigation pane, click Schema, and then click Create Schema.

  3. On the New Schema page, choose Create by Wizard in Object Storage Service.

  4. On the Configuration page, configure the parameters as prompted.

    2

    Parameters:

    • Bucket: You can select an OSS bucket of the same Alibaba Cloud primary account in the same region from the drop-down list.

    • File name: The system displays folders under the bucket you selected. Click a folder in a bucket. Sub-folders of the folder are displayed.

    • Arrow icon: You can click the arrow icon to return to the upper-layer folder.

    • Schema name: You can specify the name of the schema in this field.

  5. After the preceding parameters are configured, click Next.

  6. When you preview the schema SQL statement, you can modify the SQL statement based on your needs.

    Currently, DLA only allows you to modify CREATE clauses.

  7. After you confirm that the SQL statement is correct, click Execute. In the Execute confirmation dialog box, click OK.

step 3: Create OSS tables

DLA allows you to create OSS tables by following the wizard or by writing SQL statements. This topic describes how to create an OSS table by following the wizard.

The wizard only provides guidance for creating OSS schemas and tables. If you want to create schemas or tables for other Alibaba Cloud services or your own databases, write SQL statements.

  1. Log on to the Data Lake Analytics console.

  2. In the left-side navigation pane, click Schema. On the Schema page that appears, locate the corresponding schema, and click Create Table.

    1

  3. On the Configuration page, configure the parameters as prompted.

    If you click a folder that has subfolders, the subfolders appear, and the value of the Location field also changes with the specific folder. Click the arrow icon to return to the upper-layer folder.

    2

    Parameter Description
    Table The name of the OSS table.
    Location The data location that the system automatically sets based on the path of the folder you selected.
    Data format DLA supports files of the JSON and Text formats.
    Compression None is selected by default.
    Scan mode The method of scanning resources in the folder where the table resides.
    • Scan child folder: scans all files in the selected folder and its subfolders.
    • Do not scan child folder: scans all files in the selected folder but not its subfolders.
    Line separators (optional) The line separators that are used in Text files. You can use enter or line break as the line separator.
    Skip header rows (optional) The number of lines, starting from the first line, that are skipped when the system reads each data file.
    Column delimiter (optional) The column separator that is used in Text files. You can separate columns by using commas (,), vertical bars (|), or custom column separators.
    Column embedded level (optional) -
    Select file only (optional) The files that you want to scan.
    • Single file: Enter the name of a file, and then the system only scans this file.
    • Multiple files: Enter a wildcard, and then the system scans the files with the wildcard in their names. For example, if you enter .json, the system scans all JSON files.
  4. After you configure the preceding parameters, click Next.

  5. On the Columns page that appears, click Next.

    3

  6. On the Preview page that appears, verify that the CREATE TABLE statement is correct, and then click Execute. In the Execute dialog box that appears, click OK. The system executes the CREATE TABLE statement.

Next step

Execute SQL statements