Hive Commands With Examples in HQL

In our Previous posts we already discussed what is hive , What not is hive , Hive Architecture ,Hive features and hive create table examples and many more about hive concepts.In this post i am explaining about very basic Hive commands with examples in Hql.

We all are knew about Hive command line interface also called as Hive CLI.When we entered into hive cli we have to use some hive commands to access the features of hive.Every one know Hive almost similar to sql and almost similar commands for SQL and hive but some functionalities and some commands will not work in Hive.

Hive Commands

  • hive is one of the command to entered into hive terminal
  • hive -e command is used to execute the query without entering into hive terminal
  • $HIVE_HOME/bin/hive -e ‘select a.col from tab1 a’
  • hive -f command is used for execute one or more quires in hql script
  • $HIVE_HOME/bin/hive -f /home/my/hive-script.sql
  • .hiverc file is used for pre execute the query when you starts the hive and .hiverc file should be in your home folder not in hive folder.
  • $home/.hiverc
  • Use quit or exit to leave the interactive shell.
  • reset – Use quit or exit to leave the interactive shell.
  • set <key>=<value> –  Sets the value of a particular configuration variable (key).
  • set – Prints a list of configuration variables that are overridden by the user or Hive.
  • set -v   –    Prints all Hadoop and Hive configuration variables.
  • add files (or) jars (or) archives – Adds one or more files, jars, or archives to the list of resources in the distributed cache.
  • list files (or) jars (or) archives – Lists the resources already added to the distributed cache
  • delete files (or) jars (or) archives  <file path > – Removes the resource(s) from the distributed cache.
  • ! <command> – Executes a shell command from the Hive shell.
  • dfs <dfs command>   – Executes a dfs command from the Hive shell.
  • <query string>   –   Executes a Hive query and prints results to standard output.
  • source <filepath>  – Executes a script file inside the CLI.

hive> set mapred.reduce.tasks=32;

hive> set;

hive> select a.* from tab1;

hive> !ls;

hive> dfs -ls;

Hive Commands

Hive Commands

  • If you want comment at time of table creation or database creation just use command COMMENT ‘your comment’
  • - – is used for comments in script.hql  like — your comment
  • EXTENDED command is used for describe the database details
  • FORMATTED command is used for describe the database full details in more readable way
  • DESCRIBE command is also used for describe the database or table details but FORMATTED command is good
  • CASCADE command is used for delete the database if that database have tables;
  • RESTRICT command instead of CASCADE is equivalent to the default behavior,
  • You can set key-value pairs in the DBPROPERTIES associated with a database using the
    ALTER DATABASE command.No other metadata about the database can be changed,
    including its name and directory location:
  • hive> ALTER DATABASE financials SET DBPROPERTIES (‘edited-by’ = ‘Joe Dba’);
  • TBLPROPERTIES command is used for assign properties to the table
  •  DBPROPERTIES command is used for assign properties to the database
  • IF NOT EXISTS command used for check that table or db is already there are not

This are main basic hive commands . If you have any queries or want to add your tip, let me know via comments.please share this article to your friends.

Speak Your Mind