008-hadoop Hive SQL Syntax 3-DML operations: Metadata Storage

I. Overview

Hive does not support inserting a single line of INSERT statements, nor does IT support update operations. The data is loaded into the built-in table in load mode. Once the data is imported, it cannot be modified.

DML includes: Insert INSERT, update updates, delete delete

To load a file into a data table
• Insert query results into hive table
• 0.8 new features insert INTO

Ii. loading files into a data table

load DATA [LOCAL] inpath ' filepath ' [OVERWRITE] into TABLE tablename [PARTITION (Partcol1=val1, Partcol2=val2 ...)]
The load operation is simply a copy/move operation that moves the data file to the location corresponding to the Hive table.
filepath
• Relative paths, for example: project/data1
• Absolute path, for example:/user/hive/project/data1
• Contains the full URI of the pattern, for example: hdfs://namenode:9000/user/hive/project/data1
For example:
hive> LOAD DATA LOCAL inpath './examples/files/kv1.txt ' OVERWRITE into TABLE pokes;

Load local data, given partition information

• The loaded target can be a table or a partition. If the table contains partitions, you must specify the partition name for each partition
FilePath can refer to a file (in this case, hive will move the file to the corresponding directory in the table) or a directory (in which case, Hive will move all the files in the directory to the corresponding directory in the table)
The local keyword
• Local is specified.
the Load command will find the filepath in the local file system. If the discovery is a relative path, the path is interpreted as the current path to the current user. The user can also specify a full URI for the local file, for example: File:///user/hive/project/data1.
the Load command copies the files in the filepath to the target file system. The target file system is determined by the location properties of the table. The copied data file is moved to the location of the table's data

For example: loading local data with the given partition information:

hive> LOAD DATA LOCAL inpath './examples/files/kv2.txt ' OVERWRITE into TABLE invites PARTITION (ds= ' 2008-08-15 ');

• No local specified
if filepath points to a full uri,hive, this URI will be used directly. otherwise
• If no schema is specified or authority,hive uses schema and authority,fs.default.name defined in the Hadoop configuration file, Namenode URI is specified
• If the path is not absolute, Hive is interpreted in relation to/user/. Hive moves the contents of the file specified in filepath to the path specified in table (or partition)
load DFS data with the given partition information:
hive> LOAD DATA inpath '/user/myname/kv2.txt ' OVERWRITE into TABLE invites PARTITION (ds= ' 2008-08-15 ');
The above command would load data from a HDFS file/directory to the table. Note that loading data from HDFS would result in moving the file/directory. As a result, the operation is almost instantaneous.

OVERWRITE

• Overwrite is specified.
• The contents of the target table (or partition) are deleted, and then the contents of the file/directory pointed to by FilePath are added to the table/partition.
• If the target table (partition) already has a file, and the file name conflicts with the file name in the filepath, the existing file will be replaced by the new one.

Inserting query results into a hive table

• Insert query results into hive table
• Write query results to the HDFs file system
• Basic Mode
INSERT OVERWRITE TABLE tablename1 [PARTITION (Partcol1=val1, Partcol2=val2 ...)] Select_statement1 from From_stat Ement
• Multi-insert mode
From from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (Partcol1=val1, Partcol2=val2 ...)] Select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...
• Auto Partition mode
INSERT OVERWRITE TABLE tablename PARTITION (Partcol1[=val1], Partcol2[=val2] ...) select_statement from From_ Statement


writing query results to the HDFs file system


Insert OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... From ...
From from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 Select_statement2]
•
• Text serialization when data is written to the file system, and each column is distinguished by ^a, \ n line break


INSERT into


INSERT INTO TABLE tablename1 [PARTITION (Partcol1=val1, Partcol2=val2 ...)] Select_statement1 from from_statement

008-hadoop Hive SQL Syntax 3-DML operations: Metadata Storage