Database Reference
In-Depth Information
NOTE
Hive doesn't support temp tables. You can create tables and populate
them easily using the
CREATE TABLE .. AS
syntax, but you must
manage the lifetime of the table yourself.
After a table has been loaded, you may want to export data from it. You
can use the
INSERT .. DIRECTORY
command for this.
OVERWRITE
indicates that the target directory should be emptied before the new files are
written, and
LOCAL
indicates that the target is a directory on the local file
system. Omitting them has the same behavior as it had with the
LOAD DATA
command:
INSERT OVERWRITE LOCAL DIRECTORY
'c:\MsBigData\export_customer'
SELECT name, purchases FROM customer WHERE state =
'FL';
You can also export to multiple directories simultaneously. Be aware that
each record that meets the
WHERE
clause conditions will be exported to the
specified location, and each record is evaluated against every
WHERE
clause.
It is possible, depending on how the
WHERE
clause is written, for the same
record to be exported to multiple directories:
FROM customer c
INSERT OVERWRITE DIRECTORY '/tmp/fl_customers'
SELECT * WHERE c.state = 'FL'
INSERT OVERWRITE DIRECTORY '/tmp/ca_customers'
SELECT * WHERE c.state = 'CA';
Querying a Table
Writing queries against Hive is fairly straightforward if youare familiar with
writing SQL queries. Instead of focusing on the everyday SQL, this section
focuses on the aspects of querying Hive that differ from most relational
databases.
