Importing and Querying DWS Data

• Local import: When uploading an Excel file, ensure it is under 30 MB. For CSV files, select separators to divide data in each row and indicate if there is a table header. If there is no header, input data in each row according to the chosen table fields.
  Figure 1 Importing data from a local Excel file to a common table
  
• obs import: Choose a file from the OBS bucket or directory. Supported file types include CSV and TEXT. Set the parameters of the foreign table to be created for importing data to the OBS bucket. Use the OBS foreign table to write the OBS bucket file to the selected common table.

  Table 1 OBS import parameters

  Parameter	Description	Example Value
  storage location	Select a file or directory from the OBS bucket as the data source.	-
  file format	Select a file format from the drop-down list. Supported formats include CSV and TEXT.	CSV
  file encoding	Select a file encoding mode from the drop-down list. UTF-8 is recommended.	UTF8
  delimiter	Commas (,) are the default separators for CSV files, while tab characters are the default separators for TXT files.	,
  quote (CSV format)	Quote character used to reference fields. This parameter is used only for CSV files. The value must be a single-byte character and cannot be the same as the value of Separator or Null value.	#
  newline character (TEXT format)	When importing text data, you can specify the newline character style. The maximum length of the newline character is 10 bytes, and you can use multi-character newline characters. The supported newline characters include common ones like \r, \n, and \r\n, as well as other characters or strings like $ and #.	\r
  Whether not to escape (TEXT format)	Whether to escape the backslash (\) and its following characters in the TEXT format	Yes
  Null value	A string that represents a null value in a data file. This string will be processed as a NULL value during import.	$
  Number of data format errors	Maximum number of data format errors allowed during import. The value -1 indicates that the number of errors is not limited.	-1
  Does it contain a header (CSV format)	Whether the imported CSV file contains a header row that describes each column in the table. This parameter only applies to CSV files.	Yes
  Whether to ignore missing fields	Enabling this function sets the last column of a row in a data source file to NULL if it is missing, without reporting an error message.	Yes
  ignore extra data	Whether to ignore excessive columns when the number of columns in a source data file exceeds that defined in the foreign table	Yes
  compatible illegal chars	Enabling this function allows for invalid characters during data import. They will be replaced with question marks (?).	Yes

  Figure 2 Importing OBS bucket file data to a common table
  

Right-click the name of a partition and select View Data to add, filter, edit, or delete partition data.

Figure 4 Viewing data in a common table

• Local export: Export all SQL query results to an XLSX or CSV file. You can open the file on your local PC. A maximum of 20,000 records can be exported.
• Full export: Export all query SQL results to a specified path in an OBS bucket. By default, the results are exported to a CSV file.
  Figure 8 Full data export
  
• DDL structure export: Right-click a user schema name, click Dump SQL File, and select Yes for Whether to export data to export the DDL statements of metadata such as tables, views, functions, triggers, sequences, and synonyms, and table insertion statements to a specified path in an OBS bucket. By default, the statements are exported to an SQL file.
  Figure 9 Dumping the schema structure