只要欲分区的数据盘大于 2 TB命令如下。

[](javascript:void(0);)

试步骤

顾:由于数据库对大小写不灵敏,因此大写和小写都得以利用。

假定您就为 ECS
实例配了数据盘,您需先格式化数据盘并挂载文件系统后才能够健康下数据盘。

1. HiveQL:数据操作

(1)进入HiveQL,命令如下:

hive

图片 1

image

(2)Hive创建数据库:创建一个名吧userdb的数据库

命令:

create database userdb;

图片 2

image

时刻可经过show命令来查Hive中所蕴涵的数据库:

命令:

show databases;

图片 3

image

Hive会为每个数据库创建一个目录。数据库被的表将会以这数据库目录的子目录形式储存。

(3)Hive创建表

率先用利用use命令使用数据库userdb:

use userdb;

图片 4

image

再也冲对应要求在userdb数据库中创造建表(仅仅是创办表结构,表中并从未内容):

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n60″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

create table if not exists employee ( id int, name String, salary
String, designation String)

comment ’employee1 details’

row format delimited

fields terminated BY ‘\t’

lines terminated BY ‘\n’

stored as textfile;

</pre>

图片 5

image

(4)Hive加载数据:向建筑的表employee中加载数据

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n65″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

load data local inpath ‘/opt/sample.txt’

overwrite into table employee;

</pre>

图片 6

image

(5)查看数据是否加载成功

select * from employee;

图片 7

image

(5)Hive修改表

①. 重命名表,将表名由employee修改也emp

alter table employee rename to emp;

图片 8

image

翻看名称是否正确修改:

show tables;

图片 9

image

②. 删除表emp

drop table emp;

图片 10

image

查看是否去成功:

show tables;

图片 11

image

(6)删除数据库

①. 万一删除的数据库不也空则会报错。如下图所示:

drop database if exists userdb1;

图片 12

image

②. 若删除的数据库也空则可以成功删除,如下图所示:

drop database if exists userdb;

图片 13

image

这儿,再查数据库则会看到userdb1没有吃删除,而userdb已让剔除。

show databases;

图片 14

image

(7)使用数据库userdb1,接下去有操作都于这个数据库中尽。

use userdb1;

图片 15

image

(8)Hive分区

咱曾经来同摆分区表,名吧employee1,下面为就张表添加分区。

①. 添加分区

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n132″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

alter table employee1

add partition (year=’2012′) location ‘/opt/empdata/2012/part2012.txt’

partition (year=’2013′) location ‘/opt/empdata/2013/part2013.txt’;

</pre>

图片 16

image

②. 查看分区:

show partitions employee1;

图片 17

image

③. 写副数据:

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n143″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

load data local inpath ‘/opt/empdata/2013/file3’

into table employee1 partition(year=’2013′);

load data local inpath ‘/opt/empdata/2012/file2’

into table employee1 partition(year=’2012′);

</pre>

图片 18

image

(9)导出数据

①. 拿表employee1中之数码导出到HDFS

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n150″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

insert overwrite directory ‘/home/Test/hdfs’

row format delimited

fields terminated by ‘\t’

select * from employee1;

</pre>

图片 19

image

翻看(注意:在汇众多中翻,需要先离hiveQL,输入quit;命令来退出数据库暨root用户下执行查询命令)

hadoop fs -cat /home/Test/hdfs/000000_0

图片 20

image

②.
将表employee1中的多寡导出到地方文件系统(注意:在HiveQL中操作,需要事先上hiveQL,输入命令hive。每次又进入hiveQL都使以“use
userdb1;”命令来指定要操作的数据库。)

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n161″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

insert overwrite local directory ‘/home/Test/sample’

row format delimited

fields terminated by ‘\t’

select * from employee1;

</pre>

图片 21

image

翻看(注意:在联谊众多被查阅,需要事先退hiveQL,输入quit;命令来退出数据库及root用户下实行查询命令)

cat /home/Test/sample/000000_0

图片 22

image

③.
将表employee1中之数据导出到已存在的表employee2(注意:在HiveQL中操作,需要先上hiveQL,输入命令hive。每次重复进入hiveQL都如利用“use
userdb1;”命令来指定要操作的数据库。)

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n172″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

insert into table employee2

select * from employee1;

</pre>

图片 23

image

翻数据是否导入到表employee2中:

select * from employee2;

图片 24

image

④. 给分区重命名,并且查看重命名是否中标:

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n182″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

alter table employee1 partition (year=’2013′)

rename to partition (year=’2014′);

</pre>

图片 25

image

show partitions employee1;

图片 26

image

⑤. 删除分区,并且查看分区是否去成功:

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n191″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

alter table employee1 drop

partition (year=’2014′);

</pre>

图片 27

image

show partitions employee1;

[图表及传中…(image-78082e-1509677357636-21)]

(10)外部表的基本操作

①. 创建外部表,表名为exter_emp:

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n202″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

create external table exter_emp( id int, name String, age int, tel
String)

row format delimited

fields terminated by ‘\t’

lines terminated by ‘\n’

stored as textfile

location ‘/home/Test/External’;

</pre>

[图上传中…(image-c08f8d-1509677357636-20)]

②. 向外部表中加载数据:

load data local inpath '/opt/empdata/file4' into table exter_emp;

图片 28

image

③. 查看表中是否中标导入数据:

select * from exter_emp;

图片 29

image

④. 删除外部表exter_emp:

drop table exter_emp;

图片 30

image

⑤.
查看文件本体依然有:删除外部表底早晚,Hive仅仅删除外部表的首届数据,数据是免见面去除的。

dfs -ls /home/Test/External;

图片 31

image

注意

2. HiveQL询问操作

(1)创建外部表employees

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n232″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

create external table employees(eid int,name String,salary float,age
int,birthday date,

subordinates array<String>,

deductions map<String,float>,

address struct<street:String,city:String,state:String>)

row format delimited

fields terminated by ‘\t’

collection items terminated by ‘,’

map keys terminated by ‘:’

location ‘/home/Test/external-1’;

</pre>

图片 32

image

朝外部表employees中加载数据:

load data local inpath '/opt/empdata/file5' into table employees;

图片 33

image

(2)SELECT…FROM语句的动

①. 查看employees表中之薪饷数据

select name,salary from employees;

图片 34

image

②. 查看employees表中的下级数据信息

select name,subordinates from employees;

图片 35

image

③. 查看employees表中的扣除税捐数据

select name,deductions from employees;

图片 36

image

④. 引用复合字段查看下级数据

select name,subordinates[0] from employees;

图片 37

image

⑤. 选择一个deductions元素进行查询

select name,deductions["FedTax"] from employees;

图片 38

image

(3)使用列值计算

①. 把询问得到的员工姓名转换为那个写并盘算员工的税后薪资

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n277″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

select upper(name),salary,deductions[“FedTax”],

round(salary*(1-deductions[“FedTax”])) from employees;

</pre>

图片 39

image

②. 内置函数查询表employees中发出稍许职工,以及计算员工平均薪水

select count(*),avg(salary) from employees;

图片 40

image

③.
将employees表中每行记录着之subordinates字段内容转换成0单或基本上只新的记录行。如果某行员工记录subordinates字段内容为空的口舌,就未见面来记录;如果非为空,那么是数组的每个元素还以发出同样执行新记录:

select explode(subordinates) as sub from employees;

图片 41

image

(4)SELECT…WHERE语句的施用

翻薪资超过30000之员工

select * from employees2 where salary > 30000;

图片 42

image

(5)GROUP BY 语句的用

询问得到每个单位的职工人数之查询语句如下:

select dept,count(*) from employees2 group by dept;

图片 43

image

(6)ORDER BY语句之采取

得员工的详细信息,并将结果按部门称排序:

select id,name, dept from employees2 order by dept;

图片 44

image

(7)JOIN语句之施用

①.
底下的查询对customers和orders进行连续,找有每个客户下之订单信息。连接的尺度是customers表中之id必须同orders表中的customer_id相同。这个JOIN操作实际即便是获得每个下了订单的客户之订单情况。如果某个客户无生了其它订单,那么该客户的信息以未会见回。

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n320″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

select c.id, c.name, c.age, o.amount

from customers c join orders o

on (c.id = o.customer_id);

图片 45

image

②. HiveQL LEFT OUTER
JOIN(左外连接)返回所有行左表,即使是以右侧边的表中没有匹配。这象征,如果ON子句匹配的下手表0(零)记录,JOIN还是回到结果实行,但在右表中之各级一样列的值为NULL。这个LEFT
OUTER
JOIN操作实际就是是赢得每个客户的订单情况,不管客户是勿是生了订单,该客户之音都以见面回。通过这操作,用户可了解及全体客户的情景,包括下过订单的客户与莫生喽订单的客户。

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n325″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

select c.id, c.name, o.amount, o.time

from customers c

left outer join orders o

on (c.id = o.customer_id);

图片 46

image

③. HiveQL RIGHT OUTER
JOIN(右外接连)返回右边表的装有执行,即使以左表中没有匹配。如果ON子句的左表匹配0(零)的笔录,JOIN结果返回一行,但在左表中之各级一样列为NULL。这个RIGHT
OUTER
JOIN操作实际就是是取有订单的订单和下单客户之场面,即使有订单没有下单客户ID,也会回来该订单的景象,有关客户信息件用为NULL.

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n330″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

select c.id, c.name, o.amount, o.time

from customers c

right outer join orders o

on (c.id = o.customer_id);

图片 47

image

④. HiveQL FULL OUTER
JOIN(完全外接连)将见面返回两只说明底享有记录。如果任由一表的指定字段没有符合条件的值的语,那么即使应用NULL值替代。这个FULL
OUTER JOIN操作实际就是首先进行LEFT OUTER JOIN,再拓展RIGHT OUTER
JOIN。也就是说,首先取得具有客户之订单信息,如果没订单,相应的订单信息件就回去NULL;然后再度得到具有订单的客户信息,如果无客户,那么关于客户信息件用为NULL。

<pre class=”md-fences md-end-block” lang=”” contenteditable=”false”
cid=”n335″ mdtype=”fences” style=”box-sizing: border-box; overflow:
visible; font-family: Consolas, “Liberation Mono”, Courier, monospace;
font-size: 0.9em; white-space: pre; display: block; break-inside: avoid;
text-align: left; background-image: ; background-position:
var(–code-block-bg-color); background-size: ; background-repeat:
var(–code-block-bg-color); background-attachment: ; background-origin:
; background-clip: ; background-color: rgb(248, 248, 248); position:
relative !important; border: 1px solid rgb(221, 221, 221);
border-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px;
margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style:
normal; font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: normal; letter-spacing: normal; orphans: 2; text-indent:
0px; text-transform: none; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; text-decoration-style: initial;
text-decoration-color: initial;”>

select c.id, c.name, o.amount, o.time

from customers c

full outer join orders o

on (c.id = o.customer_id);

</pre>

图片 48

image

  • 磁盘分区和格式化是风险行为,请慎重操作。本文档描述如何处理一个新市的数据盘,如果您的数据盘上发出数量,请务必对数据盘创建快照以避免可能的数据丢失。

  • 说服务器 ECS 仅支持对 数据盘 进行分区,而不支持对 系统盘
    进行分区。只要你强行以第三正在工具对系盘进行分区操作,可能引发未知风险,如系崩溃、数据丢失等

本文描述如何用一个初的数据盘创建一个单分区数据盘并挂载文件系统。本文特适用于采取
fdisk 命令对一个无超出 2 TB
的数据盘执行分区操作。如果用分区的数据盘大于 2 TB,请参考 32TB
片存储分区。

汝还好依据作业需,对数据盘进行多分区配置。建议用系统自带的家伙进行分区操作。

操作须知

单独
置之数据盘
需要先
挂载数据盘,然后才会格式化。

随实例时一起进之数据盘,无需挂载,直接格式化。

乃需要了解挂载到实例的数据盘的设备名:您可经过 ECS 管理控制台 >
磁盘详情 > 磁盘挂载信息 查到数据盘的
设备名。数据盘的设备名由网默认分配,从 /dev/xvdb
开始于后顺序排列,分布范围包括 /dev/xvdb/dev/xvdz

操作步骤

于本示例中,我们之所以一个初的 20 GB 数据盘(设备名也
/dev/xvdb)创建一个单分区数据盘并挂载一个 ext3 文件系统。使用的实例是
I/O 优化实例,操作系统也 CentOS 6.8。

  1. 长途连接实例。

  2. 运行 fdisk -l 命令查看实例是否来数据盘。如果执行命令后,没有意识
    /dev/vdb,表示你的实例没有数据盘,无需格式化数据盘,请忽略本文后续内容。

    • 假设你的数据盘显示的是 dev/xvd?,表示你使用的好坏 I/O
      优化实例。

    • 其中 ? 是 a−z 的任一个假名。

  3. 创一个单分区数据盘,依次执行以下命令:

    1. 运行 fdisk /dev/vdb:对数据盘进行分区。

    2. 输入 n 并遵照掉车键:创建一个新分区。

    3. 输入 p
      并依掉车键:选择主分区。因为缔造的凡一个单分区数据盘,所以只是需要创造主分区。

      说明:若果一旦创造 4
      独以上之分区,您应该创建至少一个扩张分区,即挑 e

    4. 输入分区编号并遵循掉车键。因为这边仅仅创建一个分区,可以输入 1。

    5. 输入第一个可用之扇区编号:按掉车键采用默认值 1。

    6. 输入最后一个扇区编号:因为此处只有创建一个分区,所以照掉车键采用默认值。

    7. 输入 wq 并遵照掉车键,开始分区。

      <pre class=”prettyprint linenums prettyprinted”
      style=”box-sizing: border-box; margin: 0px 0px 16px; padding:
      10px; border: 1px solid rgb(221, 221, 221); outline: 0px;
      background-color: rgb(246, 246, 246); font-family: “YaHei
      Consolas Hybrid”, Consolas, “Meiryo UI”, “Malgun Gothic”, “Segoe
      UI”, “Trebuchet MS”, Helvetica, monospace, monospace; overflow:
      auto; font-size: 11.899999618530273px; line-height: 1.6;
      border-top-left-radius: 3px; border-top-right-radius: 3px;
      border-bottom-right-radius: 3px; border-bottom-left-radius: 3px;
      word-wrap: break-word; font-style: normal; font-variant-caps:
      normal; font-weight: normal; font-stretch: normal; white-space:
      pre-wrap; background-position: initial initial;
      background-repeat: initial initial;”>

      1. [root@iXXXXXXX ~]# fdisk /dev/vdb
      2. Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
      3. Building a new DOS disklabel with disk identifier 0x5f46a8a2.
      4. Changes will remain in memory only, until you decide to write them.
      5. After that, of course, the previous content won't be recoverable.
      6. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
      7. WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
      8. switch off the mode (command 'c') and change display units to
      9. sectors (command 'u').
      10. Command (m for help): n
      11. Command action
      12. e extended
      13. p primary partition (1-4)
      14. p
      15. Partition number (1-4): 1
      16. First cylinder (1-41610, default 1): 1
      17. Last cylinder, +cylinders or +size{K,M,G} (1-41610, default 41610):
      18. Using default value 41610
      19. Command (m for help): wq
      20. The partition table has been altered!
      21. Calling ioctl() to re-read partition table.
      22. Syncing disks.

      </pre>

  4. 翻开新的分区:运行命令
    fdisk -l。如果出现以下信息,说明已经成开创了新分区 /dev/vdb1。

    <pre class=”prettyprint linenums prettyprinted”
    style=”box-sizing: border-box; margin: 0px 0px 16px; padding: 10px;
    border: 1px solid rgb(221, 221, 221); outline: 0px;
    background-color: rgb(246, 246, 246); font-family: “YaHei Consolas
    Hybrid”, Consolas, “Meiryo UI”, “Malgun Gothic”, “Segoe UI”,
    “Trebuchet MS”, Helvetica, monospace, monospace; overflow: auto;
    font-size: 11.899999618530273px; line-height: 1.6;
    border-top-left-radius: 3px; border-top-right-radius: 3px;
    border-bottom-right-radius: 3px; border-bottom-left-radius: 3px;
    word-wrap: break-word; font-style: normal; font-variant-caps:
    normal; font-weight: normal; font-stretch: normal; white-space:
    pre-wrap; background-position: initial initial; background-repeat:
    initial initial;”>

    1. [root@iXXXXXXX ~]# fdisk -l
    2. Disk /dev/vda: 42.9 GB, 42949672960 bytes
    3. 255 heads, 63 sectors/track, 5221 cylinders
    4. Units = cylinders of 16065 * 512 = 8225280 bytes
    5. Sector size (logical/physical): 512 bytes / 512 bytes
    6. I/O size (minimum/optimal): 512 bytes / 512 bytes
    7. Disk identifier: 0x00053156
    8. Device Boot Start End Blocks Id System
    9. /dev/vda1 * 1 5222 41942016 83 Linux
    10. Disk /dev/vdb: 21.5 GB, 21474836480 bytes
    11. 16 heads, 63 sectors/track, 41610 cylinders
    12. Units = cylinders of 1008 * 512 = 516096 bytes
    13. Sector size (logical/physical): 512 bytes / 512 bytes
    14. I/O size (minimum/optimal): 512 bytes / 512 bytes
    15. Disk identifier: 0x5f46a8a2
    16. Device Boot Start End Blocks Id System
    17. /dev/vdb1 1 41610 20971408+ 83 Linux

    </pre>

  5. 以新分区上缔造一个文件系统:运行命令 mkfs.ext3 /dev/vdb1

    • 本示例要创建一个 ext3
      文件系统。您也足以因自己之需要,选择创建其他文件系统,例如,如果要以
      Linux、Windows 和 Mac 系统里面共享文件,您得用 mkfs.vfat
      创建 VFAT 文件系统。

    • 缔造文件系统所用时日在数据盘大小。

      <pre class=”prettyprint linenums prettyprinted”
      style=”box-sizing: border-box; margin: 0px 0px 16px; padding:
      10px; border: 1px solid rgb(221, 221, 221); outline: 0px;
      background-color: rgb(246, 246, 246); font-family: “YaHei
      Consolas Hybrid”, Consolas, “Meiryo UI”, “Malgun Gothic”, “Segoe
      UI”, “Trebuchet MS”, Helvetica, monospace, monospace; overflow:
      auto; font-size: 11.899999618530273px; line-height: 1.6;
      border-top-left-radius: 3px; border-top-right-radius: 3px;
      border-bottom-right-radius: 3px; border-bottom-left-radius: 3px;
      word-wrap: break-word; font-style: normal; font-variant-caps:
      normal; font-weight: normal; font-stretch: normal; white-space:
      pre-wrap; background-position: initial initial;
      background-repeat: initial initial;”>

      1. [root@iXXXXXXX ~]# mkfs.ext3 /dev/vdb1
      2. mke2fs 1.41.12 (17-May-2010)
      3. Filesystem label=
      4. OS type: Linux
      5. Block size=4096 (log=2)
      6. Fragment size=4096 (log=2)
      7. Stride=0 blocks, Stripe width=0 blocks
      8. 1310720 inodes, 5242852 blocks
      9. 262142 blocks (5.00%) reserved for the super user
      10. First data block=0
      11. Maximum filesystem blocks=4294967296
      12. 160 block groups
      13. 32768 blocks per group, 32768 fragments per group
      14. 8192 inodes per group
      15. Superblock backups stored on blocks:
      16. 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
      17. 4096000
      18. Writing inode tables: done
      19. Creating journal (32768 blocks): done
      20. Writing superblocks and filesystem accounting information: done
      21. This filesystem will be automatically checked every 37 mounts or
      22. 180 days, whichever comes first. Use tune2fs -c or -i to override.

      </pre>

  6. (建议)备份 etc/fstab:运行命令
    cp /etc/fstab /etc/fstab.bak

  7. /etc/fstab 写副新分区信息:运行命令
    echo /dev/vdb1 /mnt ext3 defaults 0 0 >> /etc/fstab

    注意:Ubuntu 12.04 不支持
    barrier,所以对该体系是的命是:echo '/dev/vdb1 /mnt ext3 barrier=0 0 0' >> /etc/fstab

    设需要拿数据盘单独挂载及某文件夹,比如单独用来存放网页,请将上述命令
    /mnt 替换成所要的挂载点路径。

  8. 查看 /etc/fstab 中的初分区信息:运行命令 cat /etc/fstab

    <pre class=”prettyprint linenums prettyprinted”
    style=”box-sizing: border-box; margin: 0px 0px 16px; padding: 10px;
    border: 1px solid rgb(221, 221, 221); outline: 0px;
    background-color: rgb(246, 246, 246); font-family: “YaHei Consolas
    Hybrid”, Consolas, “Meiryo UI”, “Malgun Gothic”, “Segoe UI”,
    “Trebuchet MS”, Helvetica, monospace, monospace; overflow: auto;
    font-size: 11.899999618530273px; line-height: 1.6;
    border-top-left-radius: 3px; border-top-right-radius: 3px;
    border-bottom-right-radius: 3px; border-bottom-left-radius: 3px;
    word-wrap: break-word; font-style: normal; font-variant-caps:
    normal; font-weight: normal; font-stretch: normal; white-space:
    pre-wrap; background-position: initial initial; background-repeat:
    initial initial;”>

    1. [root@iXXXXXXX ~]# cat /etc/fstab
    2. #
    3. # /etc/fstab
    4. # Created by anaconda on Thu Feb 23 07:28:22 2017
    5. #
    6. # Accessible filesystems, by reference, are maintained under '/dev/disk'
    7. # See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
    8. #
    9. UUID=3d083579-f5d9-4df5-9347-8d27925805d4 / ext4 defaults 1 1
    10. tmpfs /dev/shm tmpfs defaults 0 0
    11. devpts /dev/pts devpts gid=5,mode=620 0 0
    12. sysfs /sys sysfs defaults 0 0
    13. proc /proc proc defaults 0 0
    14. /dev/vdb1 /mnt ext3 defaults 0 0

    </pre>

  9. 挂载文件系统:运行命令 mount /dev/vdb1 /mnt

  10. 翻看时磁盘空间和利用情况:运行命令
    df -h。如果出现新建文件系统的信息,说明挂载成功,可以下新的文件系统了。

挂载操作完成后,不需要重启实例即可开始使用新的文件系统。

<pre class="prettyprint linenums prettyprinted" style="box-sizing: border-box; margin: 0px 0px 16px; padding: 10px; border: 1px solid rgb(221, 221, 221); outline: 0px; background-color: rgb(246, 246, 246); font-family: &quot;YaHei Consolas Hybrid&quot;, Consolas, &quot;Meiryo UI&quot;, &quot;Malgun Gothic&quot;, &quot;Segoe UI&quot;, &quot;Trebuchet MS&quot;, Helvetica, monospace, monospace; overflow: auto; font-size: 11.899999618530273px; line-height: 1.6; border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; word-wrap: break-word; font-style: normal; font-variant-caps: normal; font-weight: normal; font-stretch: normal; white-space: pre-wrap; background-position: initial initial; background-repeat: initial initial;">

1.  `[root@iXXXXXXX ~]# mount /dev/vdb1 /mnt`
2.  `[root@iXXXXXXX ~]# df -h`
3.  `Filesystem  Size  Used  Avail  Use%  Mounted on`
4.  `/dev/vda1 40G  6.6G  31G  18%  /`
5.  `tmpfs 499M  0  499M  0%  /dev/shm`
6.  `/dev/vdb1 20G  173M  19G  1%  /mnt`

</pre>

相关文章