We all know that Hive read\/write data in folder level, the limit here is that by default it will only read\/write the files from\/to the folder specified. But sometimes, our input data are organized by using subfolders, then Hive cannot read them if you only specify the root folder; or you want to output to separate folders instead of putting all the output data in the same folder.<\/p>\n
For example, we have sales<\/b> data dumped to hdfs(or s3), and their path structure is like sales\/city=BEIJING\/day=20140401\/data.tsv <\/b>, as you can see, the data is partitioned by city<\/b> and day<\/b>, although we can copy all the data.tsv<\/b> to the same folder, we need to do the copy and change the filename to avoid conflict, it will be a pain if the files are a lot and huge. On the other hand, even if we do copy all the data.tsv<\/b> to the same folder, when output, we want to separate the output to different folders by city<\/b> and day<\/b>, how to do that?<\/p>\n
Can hive be smart enough to read all the subfolder’s data and output to separate folders? The answer is Yes<\/b>.<\/p>\n
<\/p>\n
Let’s use an example to see how to do this.<\/p>\n
In the following example, we try to do this. Given the SALES data and PRODUCT_COSTS data, what is the TOTAL PROFIT of each product in each city on each day.<\/p>\n
Format: ID SALE_CITY PRODUCT_NAME QUANTITY SALE_PRICE SNAPSHOT_DAY<\/p>\n
Format: ID PRODUCT_NAME PRODUCT_COST SNAPSHOT_DAY<\/p>\n
The the Profit of each record is: (SALE_PRICE – PRODUCT_COST) * QUANTITY <\/b>
\nFormat: PRODUCT_NAME QUANTITY TOTAL_PROFIT<\/p>\n1.Hive configuration<\/h1>\n
At first, we need to enable the dynamic partition<\/b> functionality of HIVE. For the input, we can only specify the LOCATION<\/b> to be the root folder, and when we create the table, we need to specify the partitions<\/b>.<\/p>\n Here is the code:<\/p>\n To let HIVE actually know all the partitions under the root folder, there are 2 ways.<\/p>\n Unfortunately, Current Hive cannot be smart enough to automatically recognize all the partitions, we have to manually add them all.<\/p>\n It will be annoying if there are a lot more partitions. But sorry there is no better way using non-modified HIVE.<\/p>\n AWS EMR’s modified HIVE has a very convenient command to let HIVE recognize all the partitions automatically. Just add the following line after the table creation.<\/p>\n And then HIVE will load all the data following the partition rules(xxx=yyy pattern)<\/b> under the given root folder.<\/p>\n Here is the code for PRODUCT_COST<\/b> table, there is no partition on it,<\/p>\n For output table, we also need to specify the partitions<\/b> when we create the table.<\/p>\n When you insert the data to the output table, (1) we need to tell HIVE that you’re writing to partitions, (2) we need append the partition data to the end of each output row.<\/b><\/p>\n For (1), we added PARTITION (CITY, DAY)<\/b> after the INSERT OVERWRITE TABLE xxx<\/b> statement. By doing this, Hive will separate the data by the partitions and output it to separate folders using the partition rules(xxx=yyy pattern)<\/b>. Here it will output to \/output\/city=BEIJING\/day=20140401 …<\/b><\/p>\n
\nput the following 2 lines at the top of your hive script:<\/p>\nset hive.exec.dynamic.partition=true;\r\nset hive.exec.dynamic.partition.mode=nonstrict;\r\n<\/pre>\n
2.Input part<\/h1>\n
DROP TABLE IF EXISTS SALES;\r\nCREATE EXTERNAL TABLE SALES (\r\n ID BIGINT,\r\n SALE_CITY STRING,\r\n PRODUCT_NAME STRING,\r\n QUANTITY INT,\r\n SALE_PRICE DOUBLE,\r\n SNAPSHOT_DAY STRING\r\n)\r\nPARTITIONED BY (CITY STRING, DAY STRING)<\/b>\r\nROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n'\r\nSTORED AS TEXTFILE LOCATION \"\/input\/sales\/\";\r\n<\/pre>\n
(a) Manually add all the partitions<\/h2>\n
ALTER TABLE SALES ADD PARTITION (city='BEIJING', day='20140401');\r\nALTER TABLE SALES ADD PARTITION (city='BEIJING', day='20140402');\r\nALTER TABLE SALES ADD PARTITION (city='TAIYUAN', day='20140401');\r\nALTER TABLE SALES ADD PARTITION (city='TAIYUAN', day='20140402');\r\n<\/pre>\n
(b) Use AWS EMR to run your HIVE script<\/h2>\n
ALTER TABLE SALES RECOVER PARTITIONS;\r\n<\/pre>\n
DROP TABLE IF EXISTS PRODUCT_COST;\r\nCREATE EXTERNAL TABLE PRODUCT_COST (\r\n ID BIGINT,\r\n PRODUCT_NAME STRING,\r\n PRODUCT_COST DOUBLE,\r\n SNAPSHOT_DAY STRING\r\n)\r\nROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n'\r\nSTORED AS TEXTFILE LOCATION \"\/input\/product_cost\/\";\r\n<\/pre>\n
3.Output part<\/h1>\n
DROP TABLE IF EXISTS PROFIT;\r\nCREATE EXTERNAL TABLE PROFIT (\r\n PRODUCT_NAME STRING,\r\n QUANTITY INT,\r\n TOTAL_PROFIT DOUBLE\r\n)\r\nPARTITIONED BY (CITY STRING, DAY STRING)<\/b>\r\nROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n'\r\nSTORED AS TEXTFILE LOCATION \"\/output\/\";\r\n<\/pre>\n
INSERT OVERWRITE TABLE PROFIT PARTITION (CITY, DAY)<\/b>\r\nSELECT\r\n s.PRODUCT_NAME,\r\n SUM(s.QUANTITY) QUANTITY,\r\n SUM( (s.SALE_PRICE - p.PRODUCT_COST) * s.QUANTITY ) TOTAL_PROFIT,\r\n s.SALE_CITY,<\/b>\r\n s.SNAPSHOT_DAY<\/b>\r\nFROM\r\nSALES s\r\nJOIN\r\nPRODUCT_COST p\r\nON (s.SNAPSHOT_DAY = p.SNAPSHOT_DAY AND s.PRODUCT_NAME = p.PRODUCT_NAME)\r\nGROUP BY s.SNAPSHOT_DAY, s.PRODUCT_NAME, s.SALE_CITY\r\n;\r\n<\/pre>\n
\nFor (2), we appended the SALE_CITY<\/b>, SNAPSHOT_DAY<\/b> at the end of the record in the order the partitions are specified.<\/p>\n