Database Reference
In-Depth Information
An Example
Let's see how to use Hive to run a query on the weather dataset we explored in earlier
chapters. The first step is to load the data into Hive's managed storage. Here we'll have
Hive use the local filesystem for storage; later we'll see how to store tables in HDFS.
Just like an RDBMS, Hive organizes its data into tables. We create a table to hold the
weather data using the CREATE TABLE statement:
CREATE TABLE records (year STRING, temperature INT, quality INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';
The first line declares a records table with three columns: year , temperature , and
quality . The type of each column must be specified, too. Here the year is a string, while
the other two columns are integers.
So far, the SQL is familiar. The ROW FORMAT clause, however, is particular to HiveQL.
This declaration is saying that each row in the data file is tab-delimited text. Hive expects
there to be three fields in each row, corresponding to the table columns, with fields separ-
ated by tabs and rows by newlines.
Next, we can populate Hive with the data. This is just a small sample, for exploratory pur-
poses:
LOAD DATA LOCAL INPATH 'input/ncdc/micro-tab/sample.txt'
OVERWRITE INTO TABLE records;
Running this command tells Hive to put the specified local file in its warehouse directory.
This is a simple filesystem operation. There is no attempt, for example, to parse the file and
store it in an internal database format, because Hive does not mandate any particular file
format. Files are stored verbatim; they are not modified by Hive.
In this example, we are storing Hive tables on the local filesystem ( fs.defaultFS is set
to its default value of file:/// ). Tables are stored as directories under Hive's warehouse
directory, which is controlled by the hive.metastore.warehouse.dir property
and defaults to /user/hive/warehouse .
Thus, the files for the records table are found in the /user/hive/warehouse/records dir-
ectory on the local filesystem:
% ls /user/hive/warehouse/records/
sample.txt
Search WWH ::




Custom Search