Commonly used Apache Hive non-SQL Statements

Commands are non-SQL statements such as setting a property or adding a resource. They can be used in HiveQL scripts or directly in the CLI or Beeline. The below commands are mostly used ones and are helpful while working with partitions, adding external jar files, and changing the configuration settings.

Show column names in the result:

SET hive.cli.print.header=true;

Show database name in the Hive prompt:

SET hive.cli.print.current.db=true;

Display only the column names and exclude the table name in the resultset.

SET hive.resultset.use.unique.column.names=false;

Set property while using with Static Partitions:

SET hive.mapred.mode=strict;

Set property while using Dynamic Partitions:

SET hive.exec.dynamic.partition=true;

SET hive.exec.dynamic.partition.mode=nonstrict;

While working with buckets, enable the property by using the following command.

SET hive.enforce.bucketing=true;

Set properties while using bucket-map-join and sorted merge.

SET hive.enforce.sortmergebucketmapjoin=false;

SET hive.auto.convert.sortmerge.join=false;

SET hive.optimize.bucketmapjoin=true;

SET hive.optimize.bucketmapjoin.sortedmerge=true;

Do let me know if you need any clarification on any of the property mentioned above.