近期致力于总结科研或者工作中用到的主要技术栈,从技术原理到常用语法,这次查缺补漏当作我的小百科。主要技术包括:

以下整理错误或者缺少的部分欢迎指正!!!

数据库常用:MySQL, HQL, Spark SQL

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。

性能对比

MySQL Hive SQL Spark SQL
数据存储系统 关系型数据库 基于Hadoop的数据仓库系统 在Spark上构建的分布式计算引擎
数据处理能力 小规模结构化数据 大规模非结构化和半结构化数据 大规模非结构化和半结构化数据
执行引擎 传统查询优化器和Executor 将查询转换为MapReduce Spark引擎
分布式存储和处理
实时查询 否(只能离线分析)
可扩展性
优势 管理索引、表分区 表分区、分桶、UDF/UDAF 提供DF接口

更详细的介绍:

常用语法

MySQL Hive SQL Spark SQL
关系 适用大部分MySQL语法 兼容hive
DDL(定义) CREATE DROP ALTER
DML(操作) INSERT DELETE UPDATE
DQL(查询) SELECT WHERE
DCL(控制) GRANT REVOKE
函数 nvl(expr1, expr2) ifnull(ex pr1, expr2) 同hive
merge into 只能使用update/delete/insert代替
join on关联条件支持不等值连接 × ×

MYSQL 常用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
CREATE/DROP DATABASE/TABLE IF NOT EXISTS db/tab; -- 创建/删除数据库/表
USE db; -- 使用指定数据库
SET SQL_SAFE_UPDATES = 0; -- 关闭安全模式
SHOW DATABASES/TABLES; -- 查询所有数据库/表名;
desc 表名; -- 查询表结构

CREATE TABLE student( -- 列名 数据类型(包括int、double、varchar、date、datetime、timestamp)
id INT primary key auto_increment,
NAME VARCHAR(20) unique,
age INT not NULL,
adress VARCHAR(10) constraint 外键名 foreign key (外键列名) reference 主表名(主表列名),
);
INSERT INTO student VALUES('1','马云','55','男', '杭州', '66', '78'),
('2', '柳青', '20', '女', '湖南', '86', NULL);

ALTER TABLE 表名 rename 新表名;
ALTER TABLE 表名 ADD/MODIFY 列名 数据类型; -- 新增/修改列名、类型
ALTER TABLE 表名 CHANGE 列名 新列别 新数据类型;
ALTER TABLE 表名 DROP 列名;
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键列名) REFERENCES department 主表名(主表列名); -- 创建完表添加外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名; -- 删除外键

DELETE FROM student WHERE id = 9;
UPDATE student SET age = 99 WHERE id = 9;
DROP TABLE 表名;

SELECT
(DISTINCT) 字段列表 (as 别名)
FROM
表名列表
WHERE
条件列表 -- where在分组前限定,后不可跟聚合函数
group by
分组字段
having
分组之后的条件 -- having在分组后限定,后可进行聚合函数的判断
order by
排序字段 ASC/DESC -- (ASC默认升序,DESC降序)
limit
分页限制 -- 开始索引,每页查询条数

-- as起别名; 计算math和english分数之和
SELECT math, english, math + IFNULL(english, 0) [AS] 总分 FROM student; -- 如果有null参与运算,计算结果都为null

-- 查询姓名第二个字是化的;通配符:%任意多个字符,_单个字符
SELECT * FROM student WHERE NAME LIKE '_化%';

-- 聚合函数;按性别分组分别查询男女生的平均分、人数,要求数学分数高于70分,分组后人数要大于两个
SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

-- 笛卡尔积
SELECT * FROM dept, emp;

-- 内连接
SELECT emp.`name`,emp.`gender`,dept.`name` FROM emp,dept WHERE emp.`dept_id` = dept.`id`; -- 隐式内连接
SELECT * FROM emp [INNER] JOIN dept ON emp.`dept_id` = dept.`id`;

-- 外连接
SELECT t1.*,t2.name FROM emp t1 LEFT [outer] JOIN dept t2 ON t1.`dept_id` = t2.`id`; -- 左外连接:左表所有数据及交集部分
SELECT t1.*,t2.name FROM dept t2 RIGHT [outer] JOIN emp t1 ON t1.`dept_id` = t2.`id`; -- 右外连接:右表所有数据及交集部分

/* 子查询 */
-- 子查询的结果是单行单列的,子查询可作为条件,使用运算符判断
SELECT * FROM emp WHERE emp.`salary` < (SELECT AVG(salary) FROM emp);
-- 子查询的结果是多行单列的,子查询可作为条件,使用运算符in判断
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '市场部' OR NAME = '财务部');
-- 子查询的结果是多行多列的,子查询可作为一张虚拟表参与查询
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.`id` = t2.dept_id;

-- 常用函数
select if(boolean testCondition, T valueTrue, T valueFalseOrNull) from user;
case when 条件 then
...
when 条件 thenelse 0 end 字段名;
CONCAT(str1,str2,…) -- 连接字符串
CONCAT_WS(separator,str1,str2,...) -- 以分隔符连接字符串
GROUP_CONCAT( [distinct] 要连接的字段 [group by 字段] [separator '分隔符']) -- 分组连接字段

-- with as;相当于创建e、d临时表
with
e as (select * from scott.emp),
d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;

-- union all多个select不去重不排序;union [distinct] 去重且排序
select id,score from union_test1 union [distinct] select id, score from union_test2;

-- json data
jsonData = '{
"message":{
"location":[{"county":"浦东","city":"上海"},
{"county":"西直门","city":"北京"}]
} }' --jsonData列数据为字符串格式,外面是单引号,里面是双引号
select get_json_object(jsonData,'$.message.location[0].city') from test --输出:上海

hive常用命令

1
2
3
4
5
6
7
8
9
hive # hive启动!
hive -e "sql语句" # 不进入hive交互窗口执行sql语句
hive -f xxx.sql # 不进入hive交互窗口执行脚本中的sql语句
hive>exit/quit; # 退出hive窗口
hive>dfs -ls /; # 查看hdfs文件系统
hive>! ls /opt/module/datas; # 查看本地文件系统
cat .hivehistory # 查看在hive中输入的所有历史命令
desc formatted table_name; # 查询某个表的HDFS地址
dfs -ls address_url; # 进入该地址中可以看到相应分区的最后更新时间

分区表常用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 创建分区表常见写法
create table if not exists student2(
id int, name string
)
[COMMENT table_comment]
partitioned by (month string) -- [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] -- 分区表
[CLUSTERED BY (col_name, col_name, ...) -- 分桶表
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
row format delimited fields terminated by '\t' --[ROW FORMAT row_format]
stored as textfile -- [STORED AS file_format]
location '/user/hive/warehouse/student2'; -- [LOCATION hdfs_path]

-- 同时创建/删除多个分区
alter table dept_partition add/drop partition(month='201705'), partition(month='201704');

-- 查看分区表有多少分区
show partitions dept_partition;
-- 查看分区表结构
desc formatted dept_partition;

-- 从本地文件覆盖/追加表数据
load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table 表名 [partition (partcol1=val1,…)];

高级操作merge into

1
2
3
4
5
6
7
8
9
MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE -- DELETE
SET col1 = col1_val1,
col2 = col2_val2
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);