SAP HANA 导入平面文件数据

导入数据的步骤：

1）准备数据         平面文件(cvs文件)

2）写control file

IMPORT DATA INTO TABLE [table_name] FROM [csv_file] <option_list>

<option_list> := <option_list> <import_from_option>

<import_from_option> :=

           THREADS <num_of_threads> |

           BATCH <num_of_batchsize> |

           TABLE LOCK |   NO TYPE CHECK |

           SKIP FIRST <num_of_skip_row> ROW |

           COLUMN LIST IN FIRST ROW |COLUMN LIST ‘(‘ <column_name_list> ‘)’ |

           RECORD DELIMITED BY ‘<record_delimiter>’ |

           FIELD DELIMITED BY ‘<field_delimiter>’ |

           OPTIONALLY ENCLOSED BY ‘<optional_enclosure>’ |

           ERROR LOG ‘<error_log_file_path>’

 

例子：

/home/myself/reposrc-2.ctl

IMPORT DATA
into table REPOSRC (
    PROGNAME NVARCHAR(40),
    DATA     BLOB
)
from  /home/myself/reposrc.csv
error log /home/myself/reposrc.bad 

3）执行:

IMPORT FROM 语法：

IMPORT FROM [<file_type>] <file_path> [INTO <table_name>] [WITH <import_from_option_list>]

<file_type> ::= CSV FILE | CONTROL FILE

<file_path> ::= <string_literal>

<table_name> ::= [<schema_name>.]<identifier>

<schema_name> ::= <identifier>

WITH <import_from_option_list> <import_from_option_list> ::= <import_from_option> ...

<import_from_option> ::= THREADS <number_of_threads>

                                           | BATCH <number_of_records_of_each_commit>

                                           | TABLE LOCK | NO TYPE CHECK

                                           | SKIP FIRST <number_of_rows_to_skip> ROW

                                           | COLUMN LIST IN FIRST ROW

                                           | COLUMN LIST ( <column_name_list> )

                                           | RECORD DELIMITED BY <string_for_record_delimiter>

                                           | FIELD DELIMITED BY <string_for_field_delimiter>

                                           | OPTIONALLY ENCLOSED BY <character_for_optional_enclosure>

                                           | DATE FORMAT <string_for_date_format>

                                           | TIME FORMAT <string_for_time_format>

                                           | TIMESTAMP FORMAT <string_for_timestamp_format>

                                           | ERROR LOG <file_path_of_error_log>

举例：

 1)   IMPORT FROM '/home/temp/controlfile.ctl‘

 2)    IMPORT FROM '/home/temp/controlfile.ctl' WITH THREADS 10 BATCH 50000