LOAD DATA

Function

This command is used to load user data of a particular type, so that CarbonData can provide good query performance.

Only the raw data on HDFS can be loaded.

Syntax

LOAD DATA INPATH 'folder_path' INTO TABLE [db_name.]table_name OPTIONS(property_name=property_value, ...);

Parameter Description

Precautions

The following configuration items are involved during data loading:

• DELIMITER: Delimiters and quote characters provided in the load command. The default value is a comma (,).

  OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"')

  You can use 'DELIMITER'='\t' to separate CSV data using tabs.

  OPTIONS('DELIMITER'='\t')

  CarbonData also supports \001 and \017 as delimiters.

  

  When the delimiter of CSV data is a single quotation mark ('), the single quotation mark must be enclosed in double quotation marks (" "). For example, 'DELIMITER'= "'".

• QUOTECHAR: Delimiters and quote characters provided in the load command. The default value is double quotation marks (").

  OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"')

• COMMENTCHAR: Comment characters provided in the load command. During data loading, if there is a comment character at the beginning of a line, the line is regarded as a comment line and data in the line will not be loaded. The default value is a pound key (#).

  OPTIONS('COMMENTCHAR'='#')

• FILEHEADER: If the source file does not contain any header, add a header to the LOAD DATA command.

  OPTIONS('FILEHEADER'='column1,column2')

• ESCAPECHAR: Is used to perform strict verification of the escape character on CSV files. The default value is backslash (\).

  OPTIONS('ESCAPECHAR'='\')

  

  Enter ESCAPECHAR in the CSV data. ESCAPECHAR must be enclosed in double quotation marks (" "). For example, "a\b".

• Bad records handling:

  In order for the data processing application to provide benefits, certain data integration is required. In most cases, data quality problems are caused by data sources.

  Methods of handling bad records are as follows:

  • Load all of the data before dealing with the errors.
  • Clean or delete bad records before loading data or stop the loading when bad records are found.

  There are many options for clearing source data during CarbonData data loading, as listed in Table 2.

  Table 2 Bad Records Logger

  Configuration Item	Default Value	Description
  BAD_RECORDS_LOGGER_ENABLE	false	Whether to create logs with details about bad records
  BAD_RECORDS_ACTION	FAIL	The four types of actions for bad records are as follows: FORCE: Auto-corrects the data by storing the bad records as NULL. REDIRECT: Bad records are written to the raw CSV instead of being loaded. IGNORE: Bad records are neither loaded nor written to the raw CSV. FAIL: Data loading fails if any bad records are found. NOTE: In loaded data, if all records are bad records, BAD_RECORDS_ACTION is invalid and the load operation fails.
  IS_EMPTY_DATA_BAD_RECORD	false	Whether empty data of a column to be considered as bad record or not. If this parameter is set to false, empty data ("",', or,) is not considered as bad records. If this parameter is set to true, empty data is considered as bad records.
  BAD_RECORD_PATH	-	HDFS path where bad records are stored. The default value is Null. If bad records logging or bad records operation redirection is enabled, the path must be configured by the user.

  Example:

  LOAD DATA INPATH 'filepath.csv' INTO TABLE tablename OPTIONS('BAD_RECORDS_LOGGER_ENABLE'='true', 'BAD_RECORD_PATH'='hdfs://hacluster/tmp/carbon', 'BAD_RECORDS_ACTION'='REDIRECT', 'IS_EMPTY_DATA_BAD_RECORD'='false');

  

  If REDIRECT is used, CarbonData will add all bad records into a separate CSV file. However, this file must not be used for subsequent data loading because the content may not exactly match the source record. You must clean up the source record for further data ingestion. This option is used to remind you which records are bad.

• MAXCOLUMNS: (Optional) Specifies the maximum number of columns parsed by a CSV parser in a line.

  OPTIONS('MAXCOLUMNS'='400')

  Table 3 MAXCOLUMNS

  Name of the Optional Parameter	Default Value	Maximum Value
  MAXCOLUMNS	2000	20000

  Table 4 Behavior chart of MAXCOLUMNS

  MAXCOLUMNS Value	Number of Columns in the File Header	Final Value Considered
  Not specified in Load options	5	2000
  Not specified in Load options	6000	6000
  40	7	Max (column count of file header, MAXCOLUMNS value)
  22000	40	20000
  60	Not specified in Load options	Max (Number of columns in the first line of the CSV file, MAXCOLUMNS value)

  

  There must be sufficient executor memory for setting the maximum value of MAXCOLUMNS Option. Otherwise, data loading will fail.

Scenarios

To load a CSV file to a CarbonData table, run the following statement:

LOAD DATA INPATH 'folder path' INTO TABLE tablename OPTIONS(property_name=property_value, ...);

Examples

LOAD DATA inpath 'hdfs://hacluster/src/test/resources/data.csv' INTO table carbontable

options('DELIMITER'=',',

'QUOTECHAR'='"',

'COMMENTCHAR'='#',

'ESCAPECHAR'='\',

'FILEHEADER'='empno,empname,designation,doj,

workgroupcategory,workgroupcategoryname,

deptno,deptname,projectcode,projectjoindate,

projectenddate,attendance,utilization,salary',

'DATEFORMAT' = 'yyyy-MM-dd'

);

System Response

Success or failure will be recorded in the driver logs.