@Dinesh Chitlangia ... View more

@Aditya Sirna thank you for your response. I have tried that too, this way it is still creating a folder with name 'finename.txt' and one single file 'part-00000' file in the folder. Kindly let me know if there is a way to create a file directly on specified path ... View more

@rahul gulati - to address your scenario, you will have to consider imposing Partitions wisely on source table Based on data size on each partition, you can load data in chunks to target table hope this helps ... View more

@ Joe Olson Here you are trying to join large datasets, one of which is low billions lets assume table1: record count 800 Million , 20 columns, data size 4GB table2: record count 4 Million, 10 columns 500 MB When you are trying to Inner/outer join on these 2 tables select A.*, B.Col2 from table1 A, table2 B where A.Col1 = B.Col1; when we execute such query YARN will join both datasets and generate huge internal dataset which would be greater than 10 - 15 GB to avoid above scenario, we will split query get key columns from both tables Table1 - Column 1 Table2 - Column 2 and we are interseted in getting column2 from table2. This would require to create few temporary tables Create Table1_temp as Select Col1 from Table1 Create Table2_temp as Select Col2 from Table2 Crete Table_columnsfromTable2 Select A.Col1, B.Col2 from table1 A, table2 B where A.Col1 = B.Col1; this would certainly generate lesses data set while performing join Now join Table_columnsfromTable2 temporary table with Table1, to get your desired output select A.*, B.Col2 from table1 A, Table_columnsfromTable2 B where A.Col1 = B.Col1; Tuning of HQL varies for each scenario In some cases we can partition and have buckets. Some cases may need to look into if incoming data is sorted, number of files. If possible create Temporary table. ... View more

@Varun Please see the below to control number of Reducers setting MAPRED.REDUCE.TASKS = -1 -- this property lets Tez determine the no of reducers to initiate hive.tez.auto.reducer.parallelism = true; --this property is enabled to TRUE, hive will estimate data sizes and set parallelism estimates. Tez will sample source vertices, output sizes and adjust the estimates at run time this is the 1st property that determines initial number of reducers once Tez starts the query hive.tex.min.partition.factor =0.25;-- when auto parallelism enable, this property will be used to put a lower limit to number of reducers that Tez specified 1. hive.tez.max.partition.factor - 2.0; -- this property specifies,over-partition data in shuffle edges 2.hive.exec.reducers.max by default is 1099 --max number of reducers 3.hive.exec.reducers.bytes.per.reducer = 256 MB; which is 268435456 bytes Now to calculate the number of reducers we will need to put altogether, along with this formula also from Explain plan we will need to get the size of output, lets assume 200,000 bytes Max(1, Min(hive.exec.reducers.max [1099], Reducer Stage estimate/hive.exec.reducers.bytes.per.reducer)) x hive.tez.max.partition.factor [2] Max(1, Min(1099, 200000/268435456)) x 2 =MAX(1,min(1099,0.00074505805)) X 2 =MAX(1,0.0007) X 2 = 1 X 2 = 2 Tez will spawn 2 Reducers. In this case we can legally make Tez initiate higher number of reducers by modifying value of hive.exec.reducers.bytes.per.reducer by setting it to 20 KB =Max(1,min(1099,20000/10432)) X 2 =Max(1,19) X 2 = 38 Please note higher number of reducers doesn't mean better performance ... View more

@Varun R Optimization varies in every case. Depends on incoming data, file size. In general please use these setting for fine tuning Enable predicate pushdown (PPD) to filter at the storage layer: SET hive.optimize.ppd=true; SET hive.optimize.ppd.storage=true Vectorized query execution processes data in batches of 1024 rows instead of one by one: SET hive.vectorized.execution.enabled=true; SET hive.vectorized.execution.reduce.enabled=true; Enable the Cost Based Optimizer (COB) for efficient query execution based on cost and fetch table statistics: SET hive.cbo.enable=true; SET hive.compute.query.using.stats=true; SET hive.stats.fetch.column.stats=true; SET hive.stats.fetch.partition.stats=true; Partition and column statistics from fetched from the metastsore. Use this with caution. If you have too many partitions and/or columns, this could degrade performance. Control reducer output: SET hive.tez.auto.reducer.parallelism=true; Partition table based on necessary column, also bucket the tables(wisely identify the column) Also depends on how you want to tune your Query, based on Explain Plan. Please check number of Mappers and Reducers spawnned. ... View more

@Simran Kaur I see in stdout as Oozie launcher failed. Are you trying to run Hive action in Oozie. If that's the case please use command (yarn logs -applicationId application_1499692338187_45811)to get logs, or follow the below KB article to trace for logs to debug further https://community.hortonworks.com/articles/9148/troubleshooting-an-oozie-flow.html ... View more

get hdfs path where hive table files are stored. Use hdfs dfs -du -s -h /hdfs_path to get size in readable format. ... View more