Database Reference
In-Depth Information
DROP TABLE IF EXISTS automobiles;
CREATE EXTERNAL TABLE automobiles
(
make STRING,
model STRING,
year INTEGER,
fuelType STRING,
numOfDoors INTEGER,
design STRING,
type STRING,
cylinders INTEGER,
horsePower INTEGER,
city_hwy_mpg STRING,
price FLOAT
)
ROW FORMAT DELIMITED FIELD TERMINATED BY ','
STORED AS TEXTFILE LOCATION '/user/cloudera/
automobiles/automobiles.txt';
CREATE EXTERNAL TABLE IF NOT EXISTS automakers
(
autoMaker STRING
)
ROW FORMAT DELIMITED FIELD TERMINATED BY ',';
Let's understand the preceding script. In the first line, we are setting the default data-
base and then removing the
autos
database if it exists. Next, we are creating a
totally new
autos
database and then using the
auto
database. After that, we are
dropping the
automobiles
table if it exists. Then, we are creating a new
auto-
mobiles
table using the schema as defined earlier. Then, we are passing the data
source text files so the table can be populated with appropriate content. At last, we
will also create another table named
automaker
and use the
IF NOT EXISTS
syn-
tax. This syntax means that this particular table is created only if it does not exist.
Now, we are going to execute the preceding SQL script with the Impala shell using
the following command syntax:
