Analytics with Hadoop - Big Data Made Easy: A Working Guide to the Complete Hadoop Toolset - page 261

This indicates that the columns are delimited by commas, and so the columns in the table will match the
columns in the CSV file. The final line of the command specifies the location of the data, and the table name is
specified in the form database.table (here, fuel.consumption).
When I want to remove a table, I use the DROP TABLE command. For example, I can remove the “consumption”
table from the fuel database as follows:
DROP TABLE fuel.consumption ;
External tables are useful for data feeds based on files. Rather than load the files into a database table, I could can
create an external table against the files. Then the data can be loaded from the external table as an initial step in an
ETL chain before the data is processed further.
Table Creation
The same information can be used to create an internal table with just a few tweaks of the SQL. I simply remove the
EXTERNAL keyword and leave out the DELIMITED and LOCATION options, as follows:
CREATE TABLE fuel.consumption2
(
myear STRING,
manufacturer STRING,
model STRING,
fclass STRING,
enginesz STRING,
cylinders STRING,
transmission STRING,
fuel STRING,
consumption1 STRING,
consumption2 STRING,
consumption3 STRING,
consumption4 STRING,
avefuel STRING,
co2 STRING
);
For instance, to show which tables exist for the fuel database, I can use the SQL SHOW TABLES command:
SHOW TABLES ;
The result is a single row called “consumption.” To examine the structure of a table, I use the DESCRIBE keyword:
DESCRIBE fuel.consumption;
The result now is a series of output data rows that represent the columns in the table:
0 myear string
1 manufacturer string
2 model string
3 fclass string
4 enginesz string
5 cylinders string