这篇文章将向大家介绍HANA导入导出语句的基本使用方法。
1. 数据导出语句
基本语法
EXPORT <object_name_list> AS <export_format> INTO <path> [WITH <export_option_list>]
使用EXPORT语句可以导出tables, views, column views, synonyms, sequences, or procedures等对象的定义和相关数据。行表仅支持CSV格式,列表支持BINARY和CSV格式
我们看几个例子:
--只导出指定对象的定义(导出mytab和test1两个表的定义)
EXPORT mytab,test1 AS CSV INTO '/tmp/hana_export' WITH REPLACE CATALOG ONLY
-- 导出定义和数据 (导出mytab和test1两个表的定义和数据)
EXPORT mytab,test1 AS CSV INTO '/tmp/hana_export' WITH REPLACE STRIP THREADS 2
-- 导出schema中所有对象
EXPORT tpch.*AS CSV INTO '/tmp/hana_export' WITH REPLACE STRIP THREADS 2
2. 导入语句(IMPORT)
基本语法(IMPORT)
IMPORT <object_name_list> FROM <path> [WITH <import_option_list>] [AT [LOCATION] <indexserver_host_port>]
IMPORT语句是EXPORT的反向操作。 行表支持持CSV格式,列表支持BINARY和CSV格式
示例:
--只导入指定对象的定义
IMPORT mytab,test1 AS CSV FROM '/tmp/hana_export' WITH REPLACE CATALOG ONLY THREADS 2
IMPORT mytab,test1 AS CSV FROM '/tmp/hana_export' WITH REPLACE STRIP THREADS 2
-- 导出schema中所有对象
IMPORT tpch.*AS CSV FROM '/tmp/hana_export' WITH REPLACE STRIP THREADS 2
3. 导入语句(IMPORT FROM)
基本语法
IMPORT FROM [<file_type>] <file_path> [INTO <table_name>] [WITH <import_from_option_list>] [AT [LOCATION] <indexserver_host_port>]
IMPORT FROM语句用于从文本数据文件或通过控制文件装载数据到指定表中。 类似于SAP Sybase IQ load table和Oracle sql loader。控制文件格式类似于oracle sql loader。
--导入数据文件示例
IMPORT FROM CSV FILE ‘/data/data.csv’ INTO mytab WITH
THREADS 10
BATCH 10000
LOCK TABLE --提高列表初始装载性能
RECORD DELIMITED BY ‘\n’
FIELD DELIMITED BY ‘||’
DATA FORMAT ‘YYYY-MM-DD’
ERROR LOG ‘/data/mytab.log’
--控制文件示例
IMPORT DATA
INTO TABLE mytab
FROM '/data/data.csv'
RECORD DELIMITED BY '\n'
FIELD DELIMITED BY ','
OPTIONALLY ENCLOSED BY '"'
ERROR LOG '/data/data.err'
--使用控制文件导入数据
IMPORT FROM CONTROL FILE '/data/data.ctl'
上一篇: HANA SQL DMLs语句和事物控制语句
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
12 | |
11 | |
7 | |
5 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |