{"id":1404,"date":"2016-12-08T21:11:37","date_gmt":"2016-12-08T21:11:37","guid":{"rendered":"http:\/\/62.131.51.129\/?p=1404"},"modified":"2016-12-08T21:11:37","modified_gmt":"2016-12-08T21:11:37","slug":"create-a-hive-table","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=1404","title":{"rendered":"Create a Hive table – 3 ways"},"content":{"rendered":"

In this little note, I want to show three\u00a0different ways to create a table on Hive. The first one starts with a file on HDFS that is available and we create a table upon this file. This table is defined as an external file that is exposed as a table.<\/p>\n

The code to be executed in Hive is:<\/p>\n

CREATE EXTERNAL TABLE fam\n(nummer SMALLINT,\nname STRING)\nROW FORMAT DELIMITED\nFIELDS TERMINATED BY ','\nLOCATION '\/user\/hdfs'\n<\/pre>\n
And we may say the content of the table from, say SQL Developer:<\/p>\n
 <\/p>\n
\"\"<\/a><\/p>\n
From the clause “LOCATION ‘\/user\/hdfs'”, we know that files that will deliver the data will be stored in directory \/user\/hdfs. If we want to add data to the table, we only have to copy files to that location. Let us start with two files:<\/p>\n
\"\"<\/a><\/p>\n
We then copy an additional file to this environment ( hadoop dfs -put \/tmp\/fam3 \/user\/hdfs ). This shows:<\/p>\n
\"\"<\/a><\/p>\n
 <\/p>\n
When we then start the Hive environment: ( \u00a0beeline -u jdbc:hive2:\/\/192.168.2.60:10000 -n training -p training ), we may issue the select command: select * from fam; . This provides the content of the fam table. One may see the content of the newly added fam3 table. This shows clearly how easy it is to add data to a Hadoop platform. The only thing we have to do is to add files to the correct environment. These files will be automatically recognised as part of a table.<\/p>\n
An alternative to adding data to a table is to move data within the HDFS environment. Suppose we have a file on HDFS that contains data that should be added to the table. First, we start Hive:<\/p>\n
beeline -u jdbc:hive2:\/\/192.168.2.60:10000 -n training -p training\n<\/pre>\n
I then moved data into the table:<\/p>\n
LOAD DATA INPATH '\/user\/training\/fam4' INTO TABLE fam;\n<\/pre>\n
I then saw the file being added to the data environment:
\n\"\"<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
Another way is a direct import from, say Oracle, which creates on the fly a file on HDFS and which adds the table definition to Hive. The code is:<\/p>\n
sqoop import \\\n--connect \"jdbc:oracle:thin:@(description=(address=(protocol=tcp)(host=192.168.2.2)(port=1521))(connect_data=(service_name=orcl)))\" \\\n--username scott --password binvegni \\\n--table fam \\\n--columns \"NUMMER, NAAM\" \\\n--m 1 \\\n--fields-terminated-by ',' \\\n--table device \\\n--hive-import\n<\/pre>\n
Which creates a file on hdfs:<\/p>\n
\"\"<\/a><\/p>\n
and a table in Hive that can be seen from, say Excel:<\/p>\n
\"\"<\/a><\/p>\n
A slightly different script is this. Load data from Oracle into a file on hdfs:<\/p>\n
\nsqoop import \\\n--connect \"jdbc:oracle:thin:@(description=(address=(protocol=tcp)(host=192.168.2.2)(port=1521))(connect_data=(service_name=orcl)))\" \\\n--table COUNTRIES \\\n--columns \"NAME, LOCAL\"  \\\n--num-mappers 1 \\\n--compression-codec=snappy \\\n--username ANONYMOUS \\\n--password ine13758 \\\n--warehouse-dir=\/user\/hive\/warehouse \n<\/pre>\n
with a subsequent code in hive:<\/p>\n
\nCREATE EXTERNAL TABLE COUNTRIES ( \n    NAME STRING, \n    LOCAL STRING) \nROW FORMAT DELIMITED FIELDS TERMINATED BY ',' \nLOCATION '\/user\/hive\/warehouse\/COUNTRIES';\n<\/pre>\n
 <\/p>\n
 <\/p>\n
It is good to realise that we must undertake two actions:<\/p>\n
1:An action to store the data (in a file, that is stored in a HDFS directory.<\/p>\n
2: An action to create table definitions that are stored in a metastore.<\/p>\n
\"\"<\/a><\/p>\n
 <\/p>\n
A third manner is to use the HCatalog:<\/p>\n
\nhcat -e \"CREATE TABLE prut \\\n(id INT, company STRING) \\\nROW FORMAT DELIMITED FIELDS TERMINATED BY ',' \\\nLOCATION '\/user\/prut'\"\n<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
In this little note, I want to show three\u00a0different ways to create a table on Hive. The first one starts with a file on HDFS that is available and we create a table upon this file. This table is defined as an external file that is exposed as a table. The code to be executed […]<\/p>\n","protected":false},"author":1,"featured_media":1405,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1404","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1404","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1404"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1404\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1404"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1404"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1404"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}