{"id":1434,"date":"2017-12-12T16:16:44","date_gmt":"2017-12-12T21:16:44","guid":{"rendered":"http:\/\/www.xavignu.com\/?p=1434"},"modified":"2017-12-12T16:16:44","modified_gmt":"2017-12-12T21:16:44","slug":"create-a-new-table-with-apache-hive","status":"publish","type":"post","link":"https:\/\/www.xavignu.com\/?p=1434","title":{"rendered":"Create a new table with Apache Hive"},"content":{"rendered":"

We are going to create a new table with Apache Hive<\/a> from a previous one, populate it and then perform a UNION ALL of both tables. Below is the script that will create the new table.<\/p>\n

[sql]
\n— Below script creates a new table
\nUSE testdb;
\n— show current tables
\nSHOW tables;
\n— describe mytable2, table we will use to create mytable4
\nDESCRIBE mytable2;
\n— create new table copying format from mytable2
\nCREATE TABLE mytable4 LIKE mytable2 ;<\/p>\n

SHOW tables;
\n— describe newly created table
\nDESCRIBE mytable4;
\n— select content from newly created table
\nSELECT * FROM mytable4;
\n[\/sql]<\/p>\n

We proceed executing via hive in a linux shell.<\/p>\n

hive    -f  create-new-table.hql\r\n\r\nLogging initialized using configuration in file:\/etc\/hive\/conf.dist\/hive-log4j.properties\r\nOK\r\nTime taken: 0.898 seconds\r\nOK\r\nmytable\r\nmytable2\r\nnewtable3\r\nTime taken: 0.206 seconds, Fetched: 3 row(s)\r\nOK\r\nid                  \tint                 \t                    \r\nlname               \tstring              \t                    \r\nfname               \tstring              \t                    \r\nTime taken: 0.263 seconds, Fetched: 3 row(s)\r\nOK\r\nTime taken: 0.272 seconds\r\nOK\r\nmytable\r\nmytable2\r\nmytable4\r\nnewtable3\r\nTime taken: 0.043 seconds, Fetched: 4 row(s)\r\nOK\r\nid                  \tint                 \t                    \r\nlname               \tstring              \t                    \r\nfname               \tstring              \t                    \r\nTime taken: 0.166 seconds, Fetched: 3 row(s)\r\nOK\r\nTime taken: 0.666 seconds\r\n<\/pre>\n
<\/p>\n
We now need to populate the newly created mytable4, because right now it’s empty. We will perform this task using hdfs. We will populate with text file with the below content.
\n[text]
\n4\tNew\tJohn
\n5\tMilan\tDonald
\n[\/text]<\/p>\n
Populating from hadoop cli<\/p>\n
hdfs dfs -put   mytable4-data.txt   \/user\/hive\/warehouse\/testdb.db\/mytable4\r\n<\/pre>\n
We now proceed to check the content of the newly populated table.<\/p>\n
hive -e 'use testdb; SELECT * FROM mytable4;'\r\n\r\nLogging initialized using configuration in file:\/etc\/hive\/conf.dist\/hive-log4j.properties\r\nOK\r\nTime taken: 0.875 seconds\r\nOK\r\n4\tNew\tJohn\r\n5\tMilan\tDonald\r\nTime taken: 0.953 seconds, Fetched: 2 row(s)\r\n<\/pre>\n
Which corresponds to the content we have copied from local to hadoop<\/a> fs.<\/p>\n
Now we will use UNION ALL<\/a> to perform a query on tables mytable2 and mytable4.<\/p>\n
hive -S -e 'use testdb; SELECT * FROM mytable4 UNION ALL SELECT * FROM mytable2;'\r\n1\tDoe\tJohn\r\n2\tLancaster\tWilliam\r\n3\tGentoo\tBurp\r\n4\tNew\tJohn\r\n5\tMilan\tDonald\r\n<\/pre>\n
We can see above that we have performed a query on both tables.<\/p>\n","protected":false},"excerpt":{"rendered":"
We are going to create a new table with Apache Hive from a previous one, populate it and then perform a UNION ALL of both tables. Below is the script that will create the new table. [sql] — Below script creates a new table USE testdb; — show current tables SHOW tables; — describe mytable2, […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[74],"tags":[85,86,81,84,6],"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/pTQgt-n8","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/posts\/1434"}],"collection":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1434"}],"version-history":[{"count":6,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/posts\/1434\/revisions"}],"predecessor-version":[{"id":1440,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/posts\/1434\/revisions\/1440"}],"wp:attachment":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1434"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1434"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1434"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}