开发者社区> 长征1号> 正文

[转载]导入CSV文件到SAP HANA中(Importing CSV files into SAP HANA)

简介:
+关注继续查看

Procedure

SAP HANA is offering possibility to import content of CSV file into database table. This procedure is having several steps.

Required steps will be illustrated on following example. Goal is to create table USERS in SAP HANA database and populate is with following data:

UID LAST 
FIRST 
GENDER 
COUNTRY 

KROJZL 
TOMAS 

CZ 

ANN 
MARY 

UK 

DOE 
JOHN 

US 

1.) Planning of the import

At the beginning you should take some time to read through whole procedure and answer yourself questions like:

  • Which working directory you will use
  • In which schema you will create the table

In our example we will use working directory /dropbox/S0004108322 and we will create table USERS in schema S0004108322.

2.) Prepare CSV file containing the data you will be importing

You can use spreadsheet editor to export the data or you can produce the file manually.

Ensure that there are no empty lines (especially at the end of file) as these will lead to error during import of the file.

In our example we will create file USERS.CSV with following content.

USERS.CSV

3.) Create control file specifying details for the import command

Create new file and insert IMPORT DATA command that is having following syntax:

Detail explanation of syntax is described in SAP HANA SQL Reference Guide on page 119: https://service.sap.com/~sapidb/011000358700000604922011

In order to import our CSV file we will create following control file:

USERS.CTL

Notice that we intentionally skipped unnecessary optional parts of the command (like RECORD DELIMITED BY, FIELD DELIMITED BY and OPTIONALLY ENCLOSED BY). We do not need them because we are using default values (records are separated by ',' character and each record is on separate row).

Directories and file names on Linux operating system are case sensitive. Ensure that you used proper case for both working directory and referenced CSV file name.

4.) Upload prepared files on SAP HANA server

Either create files directly on HANA server or use FTP, SFTP or other approach to upload files to HANA server.

In case that you are working on SAP HANA sandbox system provided by SAP HANA Developer Center you can use following approach:

On HANA server run following query: SELECT * FROM SYSTEM.FTP_SERVER. You will be returned FTP hostname, user and password - do not share the user or password anywhere. 
Then you can use FTP application to connect from your notebook to target hostname (ftp.sapdevcenter.com). Be sure to configure FTP to login using obtained user and password.

Create new directory with user name you are having in SAP HANA to keep files organized and upload your files.

You might not be able to see the files you uploaded. If this is the case then you need to use your FTP application to adjust directory permissions.

Our example is illustrated by image below: 
ftp.png

5.) Create target tables in SAP HANA database

If target table does not exit you need to create it before executing the import.

This can be done either graphically or using SQL Editor. In our example we will create table using SQL statement CREATE TABLE.

SQL command to create table USERS

Detail explanation of syntax is described in SAP HANA SQL Reference Guide on page 85: https://service.sap.com/~sapidb/01100035870000060492201

6.) Execute the import

As last step you need to execute the import. Open SQL Editor and use IMPORT FROM statement to start the import.

In our example we will use following statement:

SQL statement to initiate the import

Detail explanation of syntax is described in SAP HANA SQL Reference Guide on page 119: https://service.sap.com/~sapidb/01100035870000060492201

You should see the result that statement was successfully executed and amount of affected rows is 0. This is expected result.

7.) Checking result of import

Review the content of error log file. Location of error log file is defined in control file (see step 3 of this procedure).

In our example we will need to use FTP client to see the error file. It is located in same directory where we uploaded CSV and CTL files - /S0004108322/USERS.ERR

Import was successful in case that error log file is empty.

8.) Checking content of target table

Use Open Content or Open Data Preview functions to check the data stored in table.

Alternatively you can execute following query in SQL Editor to calculate amount table rows: SELECT COUNT(*) FROM <schema>.<tablename>

专注于企业信息化,最近对股票数据分析较为感兴趣,可免费分享股票个股主力资金实时变化趋势分析工具,股票交流QQ群:457394862
分类: SAP HANA

本文转自沧海-重庆博客园博客,原文链接:http://www.cnblogs.com/omygod/archive/2013/06/04/3117992.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
2万字聊聊什么是秒杀系统(中)
大家好,我是Leo 之前我们介绍了秒杀系统的五大原则,动静分离方案,二八原则,冷热处理的一些理论方案。今天我们接着上一篇,继续介绍一下大并发流量打过来之后,我们如何做一些削峰处理以及服务端的一些优化技巧。
4 0
MySQL千万数据方案调研,一不小心直接打挂我系统
大家好,我是Leo。 之前聊的RocketMQ暂时放放,目前正在调研一个千万数据的处理方案。 在准备测试数据的时候,执行了个 select 把我电脑内存打光了。然后OOM,黑屏,宕机。。
4 0
Spark SQL概述
Spark SQL概述:Spark SQL是什么,DataFrame和DataSet是什么,DataFrame和RDD有什么区别?
5 0
Vue项目:菜农管理 【maven + spring boot + RESTFul + SSM+vue + axios】
Vue项目:菜农管理 【maven + spring boot + RESTFul + SSM+vue + axios】
5 0
+关注
345
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载