On this page

Show all

Help Center/ MapReduce Service/ User Guide (Kuala Lumpur Region)/ MRS Cluster Component Operation Guide/ Using CarbonData (for MRS 3.x or Later)/ CarbonData Syntax Reference/ DML/ LOAD DATA

LOAD DATA

Updated on 2022-08-12 GMT+08:00

View PDF

Function

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

NOTE:

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

**Table 1** LOAD DATA parameters
Parameter	Description
folder_path	Path of the file or folder used for storing the raw CSV data.
db_name	Database name. If this parameter is not specified, the current database is used.
table_name	Name of a table in a database.

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.

NOTE:

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'='\')

NOTE:

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');

NOTE:

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)

NOTE:

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

If SORT_SCOPE is set to GLOBAL_SORT during table creation, you can specify the number of partitions to be used when sorting data. If this parameter is not set or is set to a value less than 1, the number of map tasks is used as the number of reduce tasks. It is recommended that each reduce task process 512 MB to 1 GB data.
OPTIONS('GLOBAL_SORT_PARTITIONS'='2')

NOTE:

To increase the number of partitions, you may need to increase the value of spark.driver.maxResultSize, as the sampling data collected in the driver increases with the number of partitions.

DATEFORMAT: Specifies the date format of the table.
OPTIONS('DATEFORMAT'='dateFormat')

NOTE:

Date formats are specified by date pattern strings. The date pattern letters in Carbon are same as in JAVA.

TIMESTAMPFORMAT: Specifies the timestamp of a table.
OPTIONS('TIMESTAMPFORMAT'='timestampFormat')

SKIP_EMPTY_LINE: Ignores empty rows in the CSV file during data loading.
OPTIONS('SKIP_EMPTY_LINE'='TRUE/FALSE')
Optional: SCALE_FACTOR: Used to control the number of partitions for RANGE_COLUMN, SCALE_FACTOR. The formula is as follows:
```
splitSize = max(blocklet_size, (block_size - blocklet_size)) * scale_factor
numPartitions = total size of input data / splitSize
```
The default value is 3. The value ranges from 1 to 300.

OPTIONS('SCALE_FACTOR'='10')
NOTE:
- If GLOBAL_SORT_PARTITIONS and SCALE_FACTOR are used at the same time, only GLOBAL_SORT_PARTITIONS is valid.
- The compaction on RANGE_COLUMN will use LOCAL_SORT by default.

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

The data in the data.csv file is as follows:

ID,date,country,name,phonetype,serialname,salary
4,2014-01-21 00:00:00,xxx,aaa4,phone2435,ASD66902,15003
5,2014-01-22 00:00:00,xxx,aaa5,phone2441,ASD90633,15004
6,2014-03-07 00:00:00,xxx,aaa6,phone294,ASD59961,15005

CREATE TABLE carbontable(ID int, date Timestamp, country String, name String, phonetype String, serialname String,salary int) STORED AS carbondata;

LOAD DATA inpath 'hdfs://hacluster/tmp/data.csv' INTO table carbontable

options('DELIMITER'=',');

System Response

Success or failure will be recorded in the driver logs.

Parent topic: DML

Previous topic: DML

Next topic: UPDATE CARBON TABLE

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot