hadoopsters hadoopsters

## random_proportion_control.hql
select * from my_table
where rand() <= 0.0001
distribute by rand()
sort by rand()
limit 10000;

## random_distribution.hql
select * from my_table
distribute by rand()
sort by rand()
limit 10000;

## sort_by_rand.hql
select * from my_table
sort by rand()
limit 10000;

## order_by_rand.hql
select * from my_table
order by rand()
limit 10000;

## limiting.hql
select * from my_table
limit 10000;

## ctas_hive_table.hql
CREATE TABLE my_database.my_table
STORED AS ORC TBLPROPERTIES('ORC.COMPRESS'='SNAPPY') as
SELECT * FROM my_database.my_other_table WHERE YEAR=2017 AND MONTH=11 AND DAY=30;

## hive_settings.hql
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;

## analyzing_columns_in_hive_tables.hql
ANALYZE TABLE my_database.my_table compute statistics for column1, column2, column3; -- column stats for non-partitioned table
ANALYZE TABLE my_database.my_table PARTITION (YEAR=2017, MONTH=11, DAY=30, HOUR=0) compute statistics for column1, column2, column3; -- column stats for single hour of partitioned table
ANALYZE TABLE my_database.my_table PARTITION (YEAR=2017, MONTH=11, DAY=30, HOUR) compute statistics for column1, column2, column3; -- column stats for a single day of partitioned table

## analyzing_partitioned_hive_tables.hql
ANALYZE TABLE my_database.my_table PARTITION (YEAR=2017, MONTH=11, DAY=30) compute statistics;

## analyzing_hive_tables.hql
ANALYZE TABLE my_database.my_table compute statistics;
	select * from my_table
	where rand() <= 0.0001
	distribute by rand()
	sort by rand()
	limit 10000;
	CREATE TABLE my_database.my_table
	STORED AS ORC TBLPROPERTIES('ORC.COMPRESS'='SNAPPY') as
	SELECT * FROM my_database.my_other_table WHERE YEAR=2017 AND MONTH=11 AND DAY=30;
	set hive.compute.query.using.stats=true;
	set hive.stats.fetch.column.stats=true;
	ANALYZE TABLE my_database.my_table compute statistics for column1, column2, column3; -- column stats for non-partitioned table
	ANALYZE TABLE my_database.my_table PARTITION (YEAR=2017, MONTH=11, DAY=30, HOUR=0) compute statistics for column1, column2, column3; -- column stats for single hour of partitioned table
	ANALYZE TABLE my_database.my_table PARTITION (YEAR=2017, MONTH=11, DAY=30, HOUR) compute statistics for column1, column2, column3; -- column stats for a single day of partitioned table