Work with Hive tables
Hive provides several ways to work with tables.
You can use the DML (Data Manipulation Language) queries to import or add data to a table. Also, you can directly put a table into Hive using HDFS commands. If the data is stored in a relational database like MySQL, Oracle, IBM DB2, etc., you can use Sqoop to efficiently transfer petabytes of data between Hadoop and Hive. In the examples throughout this section, we use SQL to load and insert data into a Hive table.
Run Hive
Before you start, make sure that your Hive is up and running. The Hive shell is available on the machine where the Hive HiveServer2 component is installed.
-
Use the ADCM web-interface to check the host with the Hive HiveServer2 component.
-
Connect via SSH to the Hive server host.
-
Run the
hivecommand:$ sudo -u hive hiveThe command output is below.
SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/lib/hadoop/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/lib/phoenix/phoenix-5.0.0-HBase-2.0-client.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/lib/phoenix/phoenix-5.0.0-HBase-2.0-hive.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/lib/phoenix/phoenix-5.0.0-HBase-2.0-pig.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/lib/phoenix/phoenix-5.0.0-HBase-2.0-thin-client.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.slf4j.impl.Log4jLoggerFactory] SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/lib/hive/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/lib/hadoop/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 = c93e27de-24b5-4a3c-8488-925926d54d78 Logging initialized using configuration in jar:file:/usr/lib/hive/lib/hive-common-3.1.1.jar!/hive-log4j2.properties Async: true Hive Session ID = 88e81126-ac59-497e-bac2-709a665d4cf2 hive>
Create a table
Use the CREATE command to create a table.
The following command creates the solar_objects table using the , delimiter to separate each column:
CREATE TABLE solar_objects(
Name STRING,
Mass INT,
Diameter INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';The output looks as follows:
OK Time taken: 0.074 seconds
Insert data into a table
Use INSERT to populate the table with data:
INSERT INTO TABLE
solar_objects
VALUES
('Sun',1989100000,1392000),
('Mercury',330,4879),
('Venus',4867,12104),
('Earth',5972,12742),
('Mars',642,6780),
('Jupiter',1898187,139822),
('Saturn',568317,116464),
('Uranus',86813,50724),
('Neptune',102413,49244);The output looks as follows:
Query ID = aai_20211116141418_4a40a2c4-1d21-422c-80ee-cf0f95f35000
Total jobs = 1
Launching Job 1 out of 1
Tez session was closed. Reopening...
Session re-established.
Session re-established.
Status: Running (Executing on YARN cluster with App id application_1637045461848_0005)
----------------------------------------------------------------------------------------------
VERTICES MODE STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
----------------------------------------------------------------------------------------------
Map 1 .......... container SUCCEEDED 1 1 0 0 0 0
Reducer 2 ...... container SUCCEEDED 1 1 0 0 0 0
----------------------------------------------------------------------------------------------
VERTICES: 02/02 [==========================>>] 100% ELAPSED TIME: 5,42 s
----------------------------------------------------------------------------------------------
Loading data to table default.solar_objects
OK
Time taken: 15.12 seconds
Select from a table
Get the data using SELECT:
SELECT * FROM solar_objects;The output looks as follows:
OK Sun 1989100000 1392000 Mercury 330 4879 Venus 4867 12104 Earth 5972 12742 Mars 642 6780 Jupiter 1898187 139822 Saturn 568317 116464 Uranus 86813 50724 Neptune 102413 49244 Time taken: 0.17 seconds, Fetched: 9 row(s)