Inceptor常用SQL
时间：2021-07-01 10:21:17
帮助过：4人阅读
、创建数据库
建一个数据库exchange_platform。
DROP DATABASE IF EXISTS exchange_platform
CASCADE;
CREATE DATABASE IF NOT EXISTS exchange_platform;
2、删除数据库
DROP DATABASE IF EXISTS exchange_platform;
3、修改 DBPROPERTIES
ALTER DATABASE exchange_platform
SET DBPROPERTIES (
‘date‘= ‘2015-12‘);
4、修改数据库owner
ALTER DATABASE exchange_platform
SET OWNER
USER alice;
数据库owner可以是Inceptor的用户也可以是角色。
5、创建表
5.1、通过定义列建表
-- 将表建在当前数据库中：
DROP TABLE IF EXISTS user_info_tab;
CREATE TABLE user_info_tab (
name STRING,
acc_num STRING,
password STRING,
citizen_id STRING,
bank_acc STRING,
reg_date DATE,
acc_level STRING
);
-- 将表建在指定数据库中：
DROP TABLE IF EXISTS exchange_platform.user_info;
CREATE TABLE exchange_platform.user_info (
name STRING,
acc_num STRING,
password STRING,
citizen_id STRING,
bank_acc STRING,
reg_date DATE,
acc_level STRING
);
-- 建HDFS外表：
DROP TABLE IF EXISTS user_info_tab;
CREATE EXTERNAL
TABLE user_info_tab (
name STRING,
acc_num STRING,
password STRING,
citizen_id STRING,
bank_acc STRING,
reg_date DATE,
acc_level STRING
);
LOAD DATA LOCAL INPATH
‘/LOCALWORKSPACE/manual-ut/manual_data/manual_crud_tmp/user_info.txt‘
OVERWRITE INTO TABLE user_info_tab;
5.2、通过拷贝表定义建表
DROP TABLE IF EXISTS exchange_platform.user_info;
CREATE TABLE IF NOT EXISTS exchange_platform.user_info
LIKE user_info;
5.3、CTAS
--用user_info表做为例子，user_info中含有帐户密码，身份证号码和银行帐户等会影响帐户安全的信息，现在用 CTAS 建一张不包含这些信息的表。
DROP TABLE IF EXISTS nonsecure_user_info ;
CREATE TABLE nonsecure_user_info
AS SELECT name, acc_num, reg_date, acc_level
FROM user_info;
6、删除表
DROP TABLE [IF EXISTS] <table_name
>;
7、修改表
7.1 重命名
ALTER TABLE <table_name
> RENAME
TO <new_table_name
>;
7.2 修改或添加TBLPROPERTIES
-- 我们可以用这个语句给表添加和修改自定义的表属性。
ALTER TABLE <table_name
> SET TBLPROPERTIES (
‘<property_name>‘ = ‘<property_value>‘ ... );
7.3 修改或添加SERDEPROPERTIES
ALTER TABLE <table_name
> SET SERDEPROPERTIES (
‘<property_name>‘ = ‘<property_value>‘ ... );
7.4 修改外表目录
ALTER TABLE <table_name
> SET LOCATION
‘<new_location>‘;
-- 将外表指向的HDFS目录改为 <new_location>。注意，执行该操作的用户必须是 <new_location> 的owner
8、清空表
-- TRUNCATE TABLE 清空表或者分区中的数据，但不删除表或分区的元数据。这个操作只能用于托管表，不能用于外表。
8.1 清空表中数据
TRUNCATE TABLE user_info;
8.2 清空指定表中指定分区的数据
TRUNCATE TABLE user_info_part PARTITION (acc_level
=‘A‘);
9、增加替换列
9.1 增加列
-- 可以将新的列加入表中，位置在所有列之后，分区之前。
DROP TABLE IF EXISTS test_change;
CREATE TABLE test_change (a
INT, b
INT, c
INT);
-- 在表test_change中添加一个新列d，类型为INT
ALTER TABLE test_change
ADD COLUMNS(d
INT);
9.2 替换列
DROP TABLE IF EXISTS test_change;
CREATE TABLE test_change (a
INT, b
INT, c
INT);
-- 将test_change中的列由(a INT, b INT, c INT)替换为(a int, b int)起到将列c删除的效果
ALTER TABLE test_change
REPLACE COLUMNS (a
INT, b
INT);
10、视图
10.1创建视图
DROP VIEW IF EXISTS non_secure_info;
CREATE VIEW non_secure_info
AS SELECT name, reg_date, acc_level
FROM user_info;
-- 注意:不支持CREATE VIEW AS SELECT … UNION SELECT
10.2 删除视图
DROP VIEW [IF EXISTS] <view_name
>;
11、导入数据
-- 将本地数据导入一张表
LOAD DATA LOCAL INPATH
‘/LOCALWORKSPACE/manualut/manual_data/manual_crud_tmp/user_info_table.txt‘
INTO TABLE user_info2;
-- 将本地数据导入一张表下的分区
LOAD DATA LOCAL INPATH
‘/LOCALWORKSPACE/manualut/manual_data/manual_crud_tmp/user_info_table_A.txt‘
INTO TABLE partition_user_info
PARTITION (acc_level = ‘A‘);
-- 将HDFS上的数据导入一张表
LOAD DATA INPATH
‘/manual_crud_hdfs/user_info3/test‘
INTO TABLE user_info2;
12、向表中插入数据
-- 一些常见的DML如 UPDATE, DELETE和 INSERT … VALUES只能对ORC事务表、Hyperbase内存表、ES 表使用。
-- 而INSERT … SELECT、SELECT则可以作用于任意类型的表
12.1 向表中插入数据并覆盖原数据
INSERT OVERWRITE
TABLE user_info2
SELECT * FROM user_info;
12.2 多次插入
-- 使用一个数据源可以将多个查询结果插入不同表中。语法
FROM user_info
INSERT INTO TABLE user_name SELECT name n
INSERT OVERWRITE
TABLE user_name_num
SELECT name, acc_num ac
INSERT INTO TABLE user_name_level
SELECT name, acc_level;
13、向文件系统中插入数据
-- 写入文件系统使用INSERT语句，但是不同于将数据INSERT进表中有INTO和OVERWRITE两种选项，将数据写入文件系统必须INSERT OVERWRITE。
13.1 将user_info中的内容写入本地某目录下。
INSERT OVERWRITE LOCAL DIRECTORY
‘/LOCALWORKSPACE/manual-ut/manual_data/user_info2/‘
ROW FORMAT DELIMITED FIELDS TERMINATED BY‘|‘
SELECT * FROM user_info;
13.2 将user_info中的内容写入HDFS上的目录下
INSERT OVERWRITE DIRECTORY
‘/manual_crud_hdfs/user_info2/‘
ROW FORMAT DELIMITED FIELDS TERMINATED BY‘|‘
SELECT * FROM user_info;
13.3 多插入
--在Inceptor中还可以用一个SQL语句将从一个数据源中检索出来的多个结果插入不同文件和表中。
FROM user_info
INSERT OVERWRITE LOCAL DIRECTORY
‘/LOCALWORKSPACE/manual-ut/manual_data/user_info4/‘SELECT name a
INSERT OVERWRITE DIRECTORY
‘/manual_crud_hdfs/user_info5/‘SELECT password b
INSERT INTO TABLE new_user
SELECT name c
INSERT OVERWRITE
TABLE user_name SELECT name d;
14、TEXT表
DROP TABLE IF EXISTS employee;
CREATE EXTERNAL
TABLE employee (id
INT, name STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,‘
LOCATION ‘/manual_crud_tmp/employee‘;
SELECT * FROM employee;
15、CSV表
DROP TABLE IF EXISTS csv_table;
CREATE EXTERNAL
TABLE csv_table
(
col1 STRING,
col2 STRING,
col3 STRING
)
STORED AS CSVFILE;
LOAD DATA LOCAL INPATH
‘/LOCALWORKSPACE/manual-ut/manual_data/manual_crud_tmp/csv1.txt‘
OVERWRITE INTO TABLE csv_table;
16、ORC表
SET transaction.type
=inceptor;
-- 创建非分区ORC表。
DROP TABLE IF EXISTS ta;
CREATE TABLE ta (name STRING, age
INT)
CLUSTERED BY (age)
INTO 2 BUCKETS
STORED AS ORC TBLPROPERTIES ("transactional"
="true");
-- 创建非分区ORC表。
DROP TABLE IF EXISTS tg;
CREATE TABLE tg (name STRING, gpa
DOUBLE)
CLUSTERED BY (name)
INTO 4 BUCKETS STORED
AS ORC
TBLPROPERTIES ("transactional"="true");
-- 创建单值分区ORC表。
DROP TABLE IF EXISTS test;
CREATE TABLE test (a
INT, b STRING, c
DOUBLE) PARTITIONED
BY (date STRING)
CLUSTERED BY (c)
INTO 8
BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"
="true");
-- 创建范围分区ORC表。
DROP TABLE IF EXISTS t5;
CREATE TABLE t5(id
INT, value
INT) PARTITIONED
BY RANGE(amount
INT) (
PARTITION less1 VALUES LESS THAN (
1),
PARTITION less10 VALUES LESS THAN (
10),
PARTITION less100 VALUES LESS THAN (
100) )
CLUSTERED BY (id)
INTO 5 BUCKETS STORED
AS ORC TBLPROPERTIES ("transactional"
="true");
-- 桶的个数对事务处理的性能有关键性的影响，我们建议您设置合理的个数，
-- 一般是CPU个数的倍数，并且每个桶平均的大小控制不要超过200MB或者一百万行记录。
17、Holodesk表
--Holodesk普通建表
DROP TABLE IF EXISTS holodeskEmployee;
CREATE TABLE holodeskEmployee(
ID INT,
Region STRING,
Sex VARCHAR(
4),
Department STRING,
Salary DECIMAL
) STORED AS HOLODESK;
Inceptor常用SQL
标签：nsa str varchar min char 事务 write 事务处理 file
