Help Center> Cloud Stream Service> SQL Syntax Reference> DDL Statement> Creating a Sink Stream> DWS Sink Stream (OBS-based Dumping)
View PDF

DWS Sink Stream (OBS-based Dumping)

Overview

Create a sink stream to export CS data to DWS through OBS-based dumping, specifically, output CS data to OBS and then import data from OBS to DWS. For details about how to import OBS data to DWS, see Concurrently Importing Data from OBS in the Data Warehouse Service Development Guide.

DWS is an online data processing database based on the public cloud infrastructure and platform and helps you mine and analyze massive sets of data. For more information about DWS, see the Data Warehouse Service Management Guide.

Precautions

  • OBS-based dumping supports intermediate files of the following two types:
    • ORC: The ORC format does not support the array data type. If the ORC format is used, create a foreign server in DWS. For details, see Creating a Foreign Server in the Data Warehouse Service Database Development Guide.
    • CSV: By default, the line break is used as the record separator. If the line break is contained in the attribute content, you are advised to configure quote. For details, see Table 1.
  • If the target table does not exist, a table is automatically created. CS data of the SQL type does not support text. If a long text exists, you are advised to create a table in the database.
  • Ensure that OBS buckets and folders have been created.

    For details about how to create an OBS bucket, see Creating a Bucket in the Object Storage Service Console Operation Guide.

    For details about how to create a folder, see Creating a Folder in the Object Storage Service Console Operation Guide.

Syntax

Syntax

CREATE SINK STREAM stream_id (
   attr_name attr_type (',' attr_name attr_type)* 
)WITH (
          type = "dws",
          region = "",
          encode = "",
          field_delimiter = "",
          quote = "",
          db_obs_server = "",
          obs_dir = "",
          username = "",
          password =  "",
          db_url = "",
          table_name = "",
          max_record_num_per_file = "",
          max_dump_file_num = "",
          dump_interval = ""
);

Description

Table 1 Syntax description

Parameter

Mandatory

Description

type

Yes

Output channel type. Value dws indicates that data is stored to DWS.

region

Yes

Region where DWS is located.

encode

Yes

Encoding format. Currently, CSV and ORC are supported.

field_delimiter

No

Separator used to separate every two attributes. This parameter needs to be configured if the CSV encoding mode is used. It is recommended that you use invisible characters as separators, for example, \u0006\u0002.

quote

No

Single byte. It is recommended that invisible characters be used, for example, u0007.

db_obs_server

No

Foreign server (for example, obs_server) that has been created in the database.

For details about how to create a foreign server, see Creating a Foreign Server in the Data Warehouse Service Database Development Guide.

You need to specify this parameter if the ORC encoding mode is adopted.

obs_dir

Yes

Directory for storing intermediate files. The directory is in the format of {Bucket name}/{Directory name}, for example, obs-a1/dir1/subdir.

username

Yes

Username for connecting to a database.

password

Yes

Password for connecting to a database.

db_url

Yes

Database connection address. The format is /ip:port/database, for example, 192.168.1.21:8000/test1.

table_name

Yes

Data table name. If no table is available, a table is automatically created.

max_record_num_per_file

Yes

Maximum number of records that can be stored in a file. If the number of records in a file is less than the maximum value, the file will be dumped to OBS after one dumping period.

max_dump_file_num

Yes

Maximum number of files that can be dumped at a time. If the number of files to be dumped is less than the maximum value, the files will be dumped to OBS after one dumping period.

dump_interval

Yes

Dumping period. The unit is second.

Example

  • Dump files in CSV format. 
    CREATE SINK STREAM car_infos (
  car_id STRING,
  car_owner STRING,
  car_brand STRING,
  car_price INT,
  car_timestamp LONG
)
WITH (
  type = "dws",
  region = "cn-north-1" ,
  encode = "csv",
  field_delimiter = "\u0006\u0006\u0002",
  quote = "\u0007",
  obs_dir = "cs-append-2/dws",
  username = "",
  password =  "",
  db_url = "192.168.1.12:8000/test1",
  table_name = "table1",
  max_record_num_per_file = "100",
  max_dump_file_num = "10",
  dump_interval = "10"
);
  • Dump files in ORC format. 
    CREATE SINK STREAM car_infos (
  car_id STRING,
  car_owner STRING,
  car_brand STRING,
  car_price INT,
  car_timestamp LONG
)
WITH (
  type = "dws",
  region = "cn-north-1" ,
  encode = "orc",
  db_obs_server = "obs_server",
  obs_dir = "cs-append-2/dws",
  username = "",
  password =  "",
  db_url = "192.168.1.12:8000/test1",
  table_name = "table1",
  max_record_num_per_file = "100",
  max_dump_file_num = "10",
  dump_interval = "10"
);
Parent topic: Creating a Sink Stream