Oracle、hive、Hbase基本操作

Oracle基本操作

概述

• Oracle 一般创建一个全局库，可以但不建议创建多个库
• Oracle 一个用户是一个库，创建不同的库就要创建不同的用户，多个数据库，就是多个实例服务，有兼容问题，可能导致整个数据库用不了
• 一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间，一旦数据文件加入进来表空间，不能删除这个数据文件，如果要删除数据文件，必须删除数据文件所属的表空间
  • 表的数据，由用户放入到某一个表里面，这个表空间会随机把这些表数据放到一个或多个数据文件中
• 全局数据库指的是物理磁盘上的数据库，一般一个机器有一个全局数据库，不建议安装多个全局库，因为一个全局库就可以存放所有的数据
• 数据库

库相关

• create database databasename – 创建库
• drop database dbname – 删除库

备份库

• 完全备份数据库 – exp demo/demo@orcl buffer = 1024 file = d: \back.dmp full = y

  demo: 用户名、密码

  buffer: 缓存大小

  file: 具体的备份文件地址

  full: 是否导出全部文件

  ignore: 忽略错误，如果表已存在，则也是覆盖

• 将数据库中的system用户与sys用户的表导出 – exp demo/demo@orcl file=d:\backup1.dmp owner=(system,sys)

• 导出指定的表 – exp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

• 按过滤条件导出 – exp demo/demo@orcl file=d:\back.dmp tables(table1) query=" where filed1 like ‘fg%’"

• 备份远程数据库的数据库 – exp 用户名/密码@远程的IP:端口/实例 file=存放的位置:\文件名称.dmp full=y

还原库

• 完整还原 – imp 账号/密码 file=文件路径\文件名.dmp full=y log=路径
• 导入指定表 – imp 账号/密码 file=文件路径\文件名.dmp tables=(table1,table2)
• 还原到远程服务器 – imp 账号/密码 file=文件路径\文件名.dmp full=y

表相关

• 创建表 – create table 表名 (col1 type1 [primary key] [auto_increment],…)
  • 使用旧表创建新表 – select * into table_new from table_old
  • 仅适用于 oracle – create table tab_new as select col1,col2,… from table_old definition only
• 删除表 – frop table tablename
• 重命名表 – alter table 表明 rename to 新表名
• 增加字段 – alter table 表名 add (字段名 字段类型 默认值 是否为空);
• 修改字段 – alter table 表名 modify (字段名 字段类型 默认值 是否为空);
• 重命名字段 – alter table 表名 rename column 列名 to 新列名 – column 为关键字
• 删除字段 – alter table 表名 drop column 字段名;
• 添加主键 – alter table 表名 add promary key(col);
• 删除主键 – alter table 表名 drop primary key(col);
• 创建索引 – create [unique] index idxname on tabname(col…)
• 删除索引 – drop index indexname
• 创建视图 – create view viewname as select statement
• 删除视图 – drop viwe viwename

数据相关

• 数据查询 – select <列名> from <表名> [where <查询条件表达式>] [order by <排序的列名>] [asc或desc]

• 全字段插入数据 – insert into 表名 values(所有列的值)

• 部分字段插入数据 – insert into 表名(列名) values(对应的值)

• 根据条件更新部分数据 – update 表名 set 列名 = 新的值 [where 条件]

• 更新全部数据 – update 表名 set 列名 = 新的值

• 根据条件删除满足条件的记录 – delete from 表名 where 条件

• 删除表中所有记录 – delete from 表名

• 提交数据 – commit

• 回滚数据 – rollback

• 数据复制

  • 表数据复制 – insert into table1 (select * from table2)
  • 复制表结构 – create table table1 select * from table2 where 1 > 2
  • 复制表结构和数据 – create table table1 select * from table2
  • 复制指定字段 – create table table1 as select id,name from table2 where 1 > 1;

• 行列转换(Decode)

  select id,name,
  
  sum(decode(course,'语文',score)) 语文,
  
  sum(decode(course,'数学',score)) 数学,
  
  ...
  
  from student
  
  group by id,name
  

• 行列转换(Case)

  select id,name,
  
  max(case when course='语文' then score else 0 end) 语文,
  
  max(case when course='数学' then score else 0 end) 数学,
  
  ...
  
  from student
  
  group by id,name
  

• 行列转换函数(wm_concat)

  select wm_concat(name),name from test – 默认都好隔开
  

  select (wm_concat(name),’ , ’ , ’ | ') from test – , 被 |替换了
  
  select id,wm_concat(name) name from test group by id – 按 id 分组合并name
  

  select * from (select name,nums from demo) pivot (sum(nums) for name in (‘苹果’ 苹果 , ‘橘子’ 橘子 , ‘葡萄’ 葡萄 , ‘芒果’ 芒果));
  

  pivot(聚合函数 for 列名 in (类型) ),其中 in(’’)中可以指定别名,in中还可以指定子查询,比如 select distinct code from customers

• Oracle中的三个角色

  • connect 角色:是授予最终用户的典型权力

    最基本的权限有：

    • 修改会话 – alter session
    • 建立聚簇 – create cluster
    • 建立数据库链接 – create database link
    • 建立序列 – create sequence
    • 建立会话 – create session
    • 建立同义词 – create synonym
    • 建立视图 – create view

  • resource 角色: 是授予开发人员的

    建立聚簇 – create cluster

    建立过程 – create procedure

    建立序列 – create sequence

    建表 – create table

    建立触发器 – create trigger

    建立类型 – create type

  • dba 角色: 拥有全部权限，是系统最高权限，只有dba才可以创建数据库结构，并且系统权限也需要dba授出，且dba用户可以操作全体用户的任意基表，包括删除

• 授予权限格式一 – grant 权限1,权限2 to 用户

• 授予权限格式二 – grant 角色 to 用户

hive基本操作

库相关

• 创建数据库 – create database 库名
• 查看数据库 – show databases
• 切换数据库 – use 库名
• 删除数据库 – drop database if exists 库名

表相关

• 创建外部表

   create external table if not exit 表名 (
  
    	id int ,
  	
    	name string,
  
    	age string
  
    ) 
  

  表创建完成之后，会在 HDFS 上的 /usr/hive/warehouse 目录创建相应的文件

• 创建分区表

  create table 表名 (
  
  id int,
  
  name string
  
  )partitioned by (country string) row format delimited fields terminated by " ";
  

• 查看所有表 – show tables

• 查看表信息 – desc 表名

• 查看拓展描述信息 – describe formatted 表名

• 删除表 – drop table 表名

  外部表删除之后，其在 HDFS 上存储的文件并不会被删除

• 表加载数据 – load data local inpath ‘路径’ into table 表名

• 导入数据(指定分区) – load data local inpath ‘路径’ into table 表名 partition(country=‘china’);

  使用 load 命令导入数据,导入 HDFS 内的文件不需要在前面加 local

• 查看数据 – select * from 表名

• 添加分片 – alter table 表名 add partition(country=“American”);

• 创建分块表 – create table 表名(字段 字段类型) clustered by (字段,一般为id) into 4 buckets row format delimited fields terminated by ’ , ’ stored as textfile;

• 创建临时表 – create table 表名 (字段名 字段类型) row format delimited fields terminated by ’ , ’

• 导入数据到临时表(insert-select方式导入) – insert into 表名 select * from 其他表

• 分区且有序 – insert into 表名 select * from 其他表 cluster by (字段名, 一般为id) into 表名 sort by (字段名,一般为id)

  cluster by 已经有了sort by 的含义

表操作

• 查询表数据 – select * from 表名

  order by 对输入做全局排序，因此只有一个 reduce，会导致当输入规模较大时，需要比较长的计算时间

  sort by 不是全局排序，其数据进入 reduce 前完成排序，因此，如果使用 sort by进行排序，且设置 mapred.reduce.tasks>1，则 sort by 只保证每个 reduce 有序，不保证所有都有序

  distributed by 根据 distributed by 指定的内容，将数据分到不同的 reduce，分发算法为 hash 散列

  cluster by 除了具有 cluster by 的功能之外，还会对该字段进行排序

  分桶和sort字段为同一个字段时，cluster by = distributed by + sort by

• 将查询结果保存到一个新的 hive 表内 – create table 表名 as select * from 其他表

• 将查询结果保存到一个存在的 hive 表内 – insert table 表名 as select * from 其他表

• 导出到目录文件(local) – insert overwrite local directory ‘路径’ select * from 表名

• 导出到目录文件(HDFS) – insert overwrite directory ‘路径’ select * from 表名

• 行转列( concat_ws (’,’,collect_set (column))) – select col1, col2,concat_ws(’,’,column(col3)) as bian from 表名 group by col1,col2

  根据主键，进行多行合并一列

  collect_list – 不去重

  collect_set – 去重

  column 的数据类型要求是string

• 列转行(lateral view explode(split(column, ‘,’)) num ) – select col1,col2,col3_new from 表名 lateral view explode(split(col3,’,’)) b as col3_new

show databases; # 查看某个数据库
use 数据库;      # 进入某个数据库
show tables;    # 展示所有表
desc 表名;            # 显示表结构
show partitions 表名; # 显示表名的分区
show create table_name;   # 显示创建表的结构

# 建表语句
# 内部表
use xxdb; 
create table xxx;
# 创建一个表，结构与其他一样
create table xxx like xxx;
# 外部表
use xxdb; 
create external table xxx;
# 分区表
use xxdb; 
create external table xxx (l int) partitoned by (d string)
# 内外部表转化
alter table table_name set TBLPROPROTIES ('EXTERNAL'='TRUE'); # 内部表转外部表
alter table table_name set TBLPROPROTIES ('EXTERNAL'='FALSE');# 外部表转内部表

# 表结构修改
# 重命名表
use xxxdb; 
alter table table_name rename to new_table_name;
# 增加字段
alter table table_name add columns (newcol1 int comment ‘新增’)；
# 修改字段
alter table table_name change col_name new_col_name new_type；
# 删除字段(COLUMNS中只放保留的字段)
alter table table_name replace columns (col1 int,col2 string,col3 string)；
# 删除表
use xxxdb; drop table table_name;
# 删除分区
# 注意：若是外部表，则还需要删除文件(hadoop fs -rm -r -f  hdfspath)
alter table table_name drop if exists partitions (d=‘2016-07-01');

# 字段类型
# tinyint, smallint, int, bigint, float, decimal, boolean, string
# 复合数据类型
# struct, array, map

# array
create table person(name string,work_locations array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
# 数据
biansutao beijing,shanghai,tianjin,hangzhou
linan changchu,chengdu,wuhan
# 入库数据
LOAD DATA LOCAL INPATH '/home/hadoop/person.txt' OVERWRITE INTO TABLE person;
select * from person;
# biansutao       ["beijing","shanghai","tianjin","hangzhou"]
# linan           ["changchu","chengdu","wuhan"]

# map
create table score(name string, score map<string,int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';
# 数据
biansutao '数学':80,'语文':89,'英语':95
jobs '语文':60,'数学':80,'英语':99
# 入库数据
LOAD DATA LOCAL INPATH '/home/hadoop/score.txt' OVERWRITE INTO TABLE score;
select * from score;
# biansutao       {"数学":80,"语文":89,"英语":95}
# jobs            {"语文":60,"数学":80,"英语":99}

# struct
CREATE TABLE test(id int,course struct<course:string,score:int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
# 数据
1 english,80
2 math,89
3 chinese,95
# 入库
LOAD DATA LOCAL INPATH '/home/hadoop/test.txt' OVERWRITE INTO TABLE test;
# 查询
select * from test;
# 1       {"course":"english","score":80}
# 2       {"course":"math","score":89}
# 3       {"course":"chinese","score":95}

# if 函数，如果满足条件，则返回A， 否则返回B
if (boolean condition, T A, T B)
# case 条件判断函数, 当a为b时则返回c；当a为d时，返回e；否则返回f
case a when b then c when d then e else f end
# 将字符串类型的数据读取为json类型，并得到其中的元素key的值
# 第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用.读取对象或数组；
get_json_object(string s, '$.key')
# url解析
# parse_url('http://facebook.com/path/p1.php?query=1','HOST')返回'facebook.com' 
# parse_url('http://facebook.com/path/p1.php?query=1','PATH')返回'/path/p1.php' 
# parse_url('http://facebook.com/path/p1.php?query=1','QUERY')返回'query=1'，
parse_url()
# explode就是将hive一行中复杂的array或者map结构拆分成多行
explode(colname)
# lateral view 将一行数据adid_list拆分为多行adid后，使用lateral view使之成为一个虚表adTable，使得每行的数据adid与之前的pageid一一对应, 因此最后pageAds表结构已发生改变，增加了一列adid
select pageid, adid from pageAds
lateral view explode(adid_list) adTable as adid
# 去除两边空格
trim()
# 大小写转换
lower(), upper()
# 返回列表中第一个非空元素,如果所有值都为空，则返回null
coalesce(v1, v2, v3, ...)
# 返回当前时间
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')
# 返回第二个参数在待查找字符串中的位置（找不到返回0）
instr(string str, string search_str)
# 字符串连接
concat(string A, string B, string C, ...)
# 自定义分隔符sep的字符串连接
concat_ws(string sep, string A, string B, string C, ...)
# 返回字符串长度
length()
# 反转字符串
reverse()
# 字符串截取
substring(string A, int start, int len)
# 将字符串A中的符合java正则表达式pat的部分替换为C;
regexp_replace(string A, string pat, string C)
# 将字符串subject按照pattern正则表达式的规则进行拆分，返回index制定的字符
# 0:显示与之匹配的整个字符串， 1：显示第一个括号里的， 2：显示第二个括号里的
regexp_extract(string subject, string pattern, int index)
# 按照pat字符串分割str，返回分割后的字符串数组
split(string str, string pat)
# 类型转换
cast(expr as type)
# 将字符串转为map, item_pat指定item之间的间隔符号，dict_pat指定键与值之间的间隔
str_to_map(string A, string item_pat, string dict_pat)
# 提取出map的key, 返回key的array
map_keys(map m)
# 日期函数
# 日期比较函数，返回相差天数，datediff('${cur_date},d)
    datediff(date1, date2)

# 增加分区
insert overwrite table table_name partition (d='${pre_date}')

# 建表语句
# 进行分区，每个分区相当于是一个文件夹，如果是双分区，则第二个分区作为第一个分区的子文件夹
drop table if exists employees;  
create table  if not exists employees(  
       name string,  
       salary float,  
       subordinate array<string>,  
       deductions map<string,float>,  
       address struct<street:string,city:string,num:int>  
) partitioned by (date_time string, type string)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/hive/...';

# hive桶
# 分区是粗粒度的，桶是细粒度的
# hive针对某一列进行分桶，对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶中
create table bucketed_user(id int, name string)
clustered by (id) sorted by (name) into 4 buckets
row format delimited
fields terminated by '\t'
stored as textfile;
# 注意，使用桶表的时候我们要开启桶表
set hive.enforce.bucketing=true;
# 将employee表中的name和salary查询出来插入到表中
insert overwrite table bucketed_user select salary, name from employees

如果字段类型是string，则通过get_json_object提取数据；
如果字段类型是struct或map,则通过col['xx']方式提取数据；

Hbase基本操作

概述

• Hbase 依赖 HDFS 来做数据存储
• Hbase 是一个通过大量廉价机器解决海量数据的高速存储和读取的分布式数据库解决方案
• Hbase 表的基本组成
  • rowkey – 行键: 用来检索记录的主键，最大长度是64KB，实际应用中长度一般为 10-100bytes，存储时，数据按照 rowkey 排序
  • column family – 列簇: 包含一组列，列在插入数据时指定，列簇是表 schema 的一部分，所以列簇在建表时候指定，列名都以列簇作为前缀，列簇多，检索慢，最好不要设置太多列簇，官网建议小于等于 3 个，最好一个
  • column – 列: 一个列簇中有很多列，并且可以不同
  • TimeStamp – 时间戳: 每个列可以存放多个版本的值，版本号就是时间戳，按照时间戳由近到远排序，Hbase 通过 Rowkey 和 Column确定的为一个存储单元成为 cell。每个 cell 都保存着同一份数据的多个版本，版本通过时间戳来索引，时间戳可以由Hbase自动赋值，也可以由客户端赋值，如果应用程序要避免版本冲突，就必须自己生成具有唯一性的时间戳，每个cell中，最新的数据排在前面，查询时候默认返回最新最近的数据，如果要查询旧版本需要指定时间戳

操作

• 创建表

  create '表名'，'列簇1'，'列簇2' 
  create '表名',{name => '列簇1'},{name => '列簇2'}
  

• 创建表并指定版本

  create '表名',{name => '列簇1',version => 版本号},{name => '列簇2',version => 版本数}
  

• 查看表的详细信息

  desc 表名
  describe 表名
  

• 向表中插入数据

  put '表名','Rowkey','列簇:标识符','值'
  

• 查询数据/获取数据 – get

  #获取表中 rowkey 为 rk0001 的所有信息
  get '表名','rk0001'
  #获取表中 orwkey 为 rk0001，info列簇的所有信息
  get '表名','rk0001','info'
  #获取表中 rowkey 为 rk0001 info列簇的 name、age 列标识符的信息
  get '表名','rk0001','info:name','info:age'
  #获取表中 rowkey 为 rk0001 ，列簇为 info 和data 的信息
  get '表名','rk0001','info','data'
  get '表名','rk0001',{column => ['info','data']}
  get '表名','rk0001',{column => ['info:name','data:pic']}
  #获取表中 rowkey 为 rk0001，列簇为 info，版本号为最新5个的信息
  get '表名','rk0001',{column => info,version => 2}
  get '表名','rk0001',{column => 'info:name',version => 5}
  get '表名','rk0001',{column => 'info:name',version => 5,timerange => [1392368783980, 1392380169184]}
  #获取表中rowkey 为 rk0001，cell的值为zhangsan 的信息
  get '表名','rk0001',{filter => "valueFilter (=,'binary:图片')"}
  #获取表中rowkey 为 rk0001，列标识符含有a的信息
  get '表名','rk0001',{filter => "(QualifierFilter(=,'substring:a'))"}
  

• 查询数据 – scan

  #查询表中的所有信息
  scan '表名'
  #查询表中的指定列簇的所有信息
  scan '表名',{column => '列簇'}
  scan '表名',{column => '列簇:列标识符'}
  scan '表名',{column => ['列簇1','列簇2']}
  #查询表中的指定列簇的所有版本信息
  scan '表名',{column => '列簇'} #只查询新值
  scan '表名',{column => '列簇',version => 版本数}
  #查询表中列簇为 info 和 data 的信息
  scan '表名',{column => ['info','data']}
  #查询表中列簇为列簇、标识符为标识符的信息，并且版本最新的5个
  scan '表名',{column => '列簇:标识符',version => 5}
  #查询表中列簇1、列簇2且标识符含 a 字符的信息
  scan '表名',{column =>['列簇1'，'列簇2']}
  scan '表名',{column =>['列簇1','列簇2'],filter => "(QualifierFilter(=,'substring:a'))"}
  #查询表中列簇，rowkey的起始偏移范围是[baiyc_20150716_0003, baiyc_20150716_0006]的数据
  scan '表名',{column => '列簇',startrow => "baiyc_20150716_0003",endrow => "baiyc_20150716_0006"}
  #查询表中 rowkey以 rk 字符开头的
  scan '表名',{filter => "prefixfilter('rk)"}
  #查询表中指定时间戳范围的数据
  scan '表名',{timerange => [1540882871681,1540882888540]}
  

• 删除数据 – delete

  #删除记录
  delete '表名','rowkey' #不能一口气删除一个 rowkey 所对应的所有 key-value
  #删除字段
  delete '表名','rowkey','列簇:列标识符'
  #删除表中 rowkey 为 rowkey，列标识符为列簇:列标识符的数据
  get '表名','rowkey','列簇:列标识符' 
  delete '表名'，'rowkey','列簇:列标识符' #实现删除，其他两句实现辅助作用
  put '表名','rowkey','列簇:列标识符','值'
  #删除表中 rowkey 为 rowkey,标识符为 列簇:列标识符,timestamp 为 timestamp 的数据
  delete '表名','rowkey','列簇:列标识符',timestamp
  

• 修改表结构 – alter

  Hbase没有修改数据的显示操作，重复插入就相当于是修改操作
  #修改两个列簇
  alter '表名',name => '列簇1'
  alter '表名',name => '列簇2'
  #删除一个列簇
  alter '表名',name => '列簇',method => 'delete'
  或
  alter '表名','delete' => '列簇'
  #添加列簇1并且删除列簇3
  alter '表名',{name => '列簇1'}，{'delete' => '列簇3'}
  #将表中的列簇1的列簇号版本改为5
  alter '表名',name => '列簇1',version => 5
  

• 清空表 – truncate ‘表名’

• 停用表/启用表

  #停用表
  disable '表名'
  #启用表
  enable '表名'
  

• 删除表

  disable '表名'  #删除之前先停用表
  drop '表名'  #停用之前才能删除表
  

p 为 timestamp 的数据
delete ‘表名’,‘rowkey’,‘列簇:列标识符’,timestamp

- 修改表结构 -- alter

```sql
Hbase没有修改数据的显示操作，重复插入就相当于是修改操作
#修改两个列簇
alter '表名',name => '列簇1'
alter '表名',name => '列簇2'
#删除一个列簇
alter '表名',name => '列簇',method => 'delete'
或
alter '表名','delete' => '列簇'
#添加列簇1并且删除列簇3
alter '表名',{name => '列簇1'}，{'delete' => '列簇3'}
#将表中的列簇1的列簇号版本改为5
alter '表名',name => '列簇1',version => 5

• 清空表 – truncate ‘表名’

• 停用表/启用表

  #停用表
  disable '表名'
  #启用表
  enable '表名'
  

• 删除表

  disable '表名'  #删除之前先停用表
  drop '表名'  #停用之前才能删除表