Get FREE Access to Data Analytics Example Codes for Data Cleaning, Data Munging, and Data Visualization How to create a table in hive by copying an existing table schema? The LOCATION keyword is used for specifying where the table should be stored on HDFS. Comments have been mentioned for each column so that anybody referring to the table gets an overview about what the columns mean.
In the above step, we have created a hive table named Students in the database college with various fields like ID, Name, fee, city, etc. Hive create table command is used to create a table in the existing database that is in use for a particular session. Usage of Use Database Command in HiveĭDL Commands on Tables in Hive Create Table Command in Hive This hive command is used to select a specific database for the session on which hive queries would be executed. Programmers can view the list of existing databases in the current schema. Let’s use the Alter command to modify the OWNER property and specify the role for the owner – ALTER (DATABASE) database_name SET OWNER user_or_role Usage of ALTER database command in Hive – Whenever the developers need to change the metadata of any of the databases, alter hive DDL command can be used as follows – ALTER (DATABASE) database_name SET DBPROPERTIES (property_name=property_value. This command is used to check any associated metadata for the databases. In the syntax for drop database Hive command, “if exists” clause is used to avoid any errors that might occur if the programmer tries to delete a database which does not exist. For deleting a database in Hive along with the existing tables, users must change the mode from RESTRICT to CASCADE. In Hadoop Hive, the mode is set as RESTRICT by default and users cannot delete it unless it is non-empty. This command is used for deleting an already created database in Hive and the syntax is as follows - DROP (DATABASE) database_name Usage of Drop Database Command in Hive hive> drop database if exists firstDB CASCADE Usage of Create Database Command in Hive hive> create database if not exists firstDB comment "This is my first demo" location '/user/hive/warehouse/newdb' with DBPROPERTIES ('createdby'='abhay','createdfor'='dezyre') In the above syntax for create database command, the values mentioned in square brackets are optional.
Let’s look at the usage of the top hive commands in HQL on both databases and tables –ĭDL Commands on Databases in Hive Create Database in HiveĪs the name implies, this DDL command in Hive is used for creating databases. DDL Commands in Hive CREATEĭatabases,Tables,Table Properties,Partitions,Functions,Index DDL commands are the statements that are responsible for defining and changing the structure of a database or table in Hive. SQL users might already be familiar with what DDL commands are but for readers who are new to SQL, DDL refers to Data Definition Language. Learn Hadoop by working on interesting Big Data and Hadoop Projects DDL Commands in Hive Hive is a friendlier data warehouse tool for users from ETL or database background who are accustomed to using SQL for querying data. Hive makes data processing on Hadoop easier by providing a database query interface to hadoop.
0 kommentar(er)
