"Source" self-learning Hadoop from zero: Hive table operations

Read Catalogue

• Order
• Create a table
• View Table
• Modify Table
• Delete a table
• Series Index

This article is copyright Mephisto and Blog Park is shared, welcome reprint, but must retain this paragraph statement, and give the original link, thank you for your cooperation.

The article is written by elder brother (Mephisto), Sourcelink

Order

In the previous article, we introduced hive and installed it, and we'll use hive for an initial explanation.

Let's start by introducing Hive's creation table, modifying tables, deleting tables, and so on.

Create a table

One: Hive Client

The terminal Input Hive command requires the Hive Client to be installed.

Two: Enter

Switch users, go to hive

Su hdfshive

　　

Three: Create a table

Grammar:

CREATE (database| SCHEMA) [IF not EXISTS] database_name  [COMMENT database_comment]  [location Hdfs_path]  [with Dbproperties ( Property_name=property_value, ...)];

Example:

CREATE TABLE student (  ID             int,  name       string,  openingtime        string);

Four: Create a table with partitions

Introduced:

A table can have one or more partitions, and each partition exists in a folder as a separate directory under the table folder. The partition is present in the table structure as a field and can be viewed by the describe table command, but the field does not hold the actual data content, only the representation of the partition . In a hive select query, the entire table content is generally scanned, and it consumes a lot of time to do unnecessary work. Sometimes you only need to scan a subset of the data in the table, so the partition concept is introduced when the table is built. A Partition in the table corresponds to a directory under the table, Partition is the auxiliary query, narrow the query scope, speed up the retrieval speed of the data and manage the data according to certain specifications and conditions.

Grammar:

CREATE TABLE table_name (  ID                int,  dtdontquery       string,  name              string) partitioned by (date String

Example:

CREATE TABLE score (  ID                int,  studentid       int,  score               Double  string);

View Table

One: View all tables

Show tables;

Two: View a table information

We use Desctribe to display information about a table.

Grammar:

DESCRIBE DATABASE [EXTENDED] Db_name;describe SCHEMA [EXTENDED] db_name; --(note:hive0.15.0and later)---------------------------------------------------------------------------------DESCRIBE [ EXTENDED|formatted] [Db_name.] Table_name[.col_name ([. Field_name]| [.'$elem $'] | [.'$key $'] | [.'$value $'] )* ]; --(note:hive1. x.x and0. x.x only)-(see"Hive 2.0+: New Syntax"Below

Example:

DESCRIBE student;

DESCRIBE score;

Three: View a column of information

DESCRIBE student. ID;

　

Modify Table

One: Change the table name

Grammar:

ALTER TABLE table_name RENAME to new_table_name;

Example:

ALTER TABLE student Rename to Student1;

Second: Modify Columns

Grammar:

ALTER TABLE table_name [PARTITION partition_spec] Change [COLUMN] col_old_name col_new_name column_type  [COMMENT Col_comment] [First | After column_name] [cascade| RESTRICT];

Example:

string;

Three: Add/Replace Columns

Grammar:

ALTER TABLE table_name [PARTITION Partition_spec]  ADD| REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)  [CASCADE| RESTRICT]

Example:

int);

Delete a table

One: Delete table

Grammar:

DROP (database| SCHEMA) [IF EXISTS] database_name [restrict| CASCADE];

Example:

drop table score;

--------------------------------------------------------------------

Here, the content of this chapter is complete.

Series Index

"Source" Self-Learning Hadoop series index from zero

This article is copyright Mephisto and Blog Park is shared, welcome reprint, but must retain this paragraph statement, and give the original link, thank you for your cooperation.

The article is written by elder brother (Mephisto), Sourcelink

"Source" self-learning Hadoop from zero: Hive table operations