HIVE: Execute sql statements in a file

Syntax

hive -f {FILE_PATH}

 

Step 1: Let’s create sql statement in a file ‘data.sql’

 

data.sql

CREATE TABLE employee (
id INT,
name STRING,
hobbies ARRAY<STRING>,
technology_experience MAP<STRING,STRING>,
gender_age STRUCT<gender:STRING,age:INT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;

Step 2: Execute data.sql file using hive command line tool.

$hive -f data.sql 
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/Cellar/hive/3.1.2_2/libexec/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/Cellar/hadoop/3.3.0/libexec/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Hive Session ID = 7b4468cf-f00b-4173-b88a-ec8e2b347ccb

Logging initialized using configuration in jar:file:/usr/local/Cellar/hive/3.1.2_2/libexec/lib/hive-common-3.1.2.jar!/hive-log4j2.properties Async: true
Hive Session ID = e2a6e609-4fa0-48a4-85bc-0bed0358b0c3
OK
Time taken: 1.433 seconds

Step 3: login to hive in interactive mode and execute the command ‘show tables’ to confirm.

hive> show tables;
OK
employee
Time taken: 0.062 seconds, Fetched: 1 row(s)

 

Previous                                                    Next                                                    Home