spark-sql 轉(zhuǎn)自

spark啟動(dòng)

spark-sql

退出

spark-sql> quit;     --退出spark-sql
或者
spark-sql> exit;

查看已有的database

show databases;
--切換數(shù)據(jù)庫(kù)
use databaseName;

創(chuàng)建數(shù)據(jù)庫(kù)

create database myDatabase;

登錄數(shù)據(jù)庫(kù)myDatabase;

use myDatabase

查看已有的table

show tables;  -- 查看所有表
show tables 'KHDX'; -- 支持模糊查詢，表名包含KHDX

創(chuàng)建表

create EXTERNAL TABLE IF NOT EXISTS  table tab_test(
name string,
age int,
num1 double,
num2 bigint,
msg varchar(80)    --最后一個(gè)字段后面不能有  ','  號(hào)
)
partitioned by (p_age int,p_name string) --分區(qū)信息
row format delimited fields terminated by ',' --數(shù)據(jù)中，屬性間用逗號(hào)分隔
stored as textfile location '/tab/test/tab_test';  --保存路徑，最后也可帶'/' 即寫成 '/tab/test/tab_test/'
--  EXTERNAL TABLE IF NOT EXISTS 推薦 
-- stored as orc ;orc類型的表，手動(dòng)推數(shù)據(jù)（txt / csv 文件；無需表頭，行尾無需','，數(shù)據(jù)文件保存為unix utf-8 無bom格式）不行;
--可以借助textfile類型的臨時(shí)表插入數(shù)據(jù)；插入時(shí)，要注意字段順序?qū)?yīng)一致。

插入數(shù)據(jù)

--指定分區(qū)，追加插入;最好不要用 'seletc *  ' 表字段變化時(shí),*指代的內(nèi)容不一樣
insert into table tab_test_orc partition(p_age=10,p_name='lucy') select name,age,num1,num2,msg from tab_test_temp;
--指定分區(qū)，覆蓋插入
insert overwrite table tab_test_orc partition(p_age=10,p_name='lucy') select name,age,num1,num2,msg from tab_test_temp;

顯示表結(jié)構(gòu)

desc khdx_hy;   -- 顯示表khdx_hy的表結(jié)構(gòu)
desc formatted khdx_hy; -- 格式化表khdx_hy的表結(jié)構(gòu)信息，信息更詳細(xì)，包括在hdfs的存儲(chǔ)位置
show partitions khdx_hy; -- 顯示表khdx_hy的分區(qū)信息
show create table khdx_hy; -- 查看建表語(yǔ)句

修改表結(jié)構(gòu)

alter table myDatabase.nbzz_ckmxz add if not exists partition(tjrq='20171231')add partition(tjrq='20180101')  -- 手動(dòng)給分區(qū)表增加2個(gè)分區(qū)
alter table myDatabase.nbzz_ckmxz drop if exists partition (tjrq='20171231');   -- 手動(dòng)刪除分區(qū)表某個(gè)分區(qū)
alter table myDatabase.nbzz_ckmxz add columns (fh string);     -- 追加字段
alter table myDatabase.nbzz_ckmxz change hydh hydh1 string;          -- 修改字段hydh名稱為hydh1，類型為string


drop table myDatabase.nbzz_ckmxz;  -- 刪除表
alter table myDatabase.tmp_nbzz_ckmxz rename to myDatabase.nbzz_ckmxz;  -- 重命名表

刪表中數(shù)據(jù)

truncate table tab_test;   --執(zhí)行后，分區(qū)依然存在
truncate table tab_test partition(p_age=10,p_name='Tom'); --刪除某分區(qū) 中數(shù)據(jù)

文件導(dǎo)出/導(dǎo)入數(shù)據(jù)

load data local inpath '/home/myDatabase/data/t_ma_fct_etl_acct_liyang_20171231.dat' overwrite into table myDatabase.T_MA_FCT_ETL_ACCT partition(tjrq="20171231");
 -- 從文件導(dǎo)入數(shù)據(jù)至表的某個(gè)分區(qū)。
insert overwrite local directory '/home/myDatabase/data/khdx_hy.txt'  row format delimited  fields terminated by '\t' select * FROM myDatabase.KHDX_HY;
 -- 從表khdx_hy導(dǎo)出數(shù)據(jù)至本地文件khdx_hy.txt

分區(qū)操作

增加分區(qū)

alter table tab_test add if not exists partition(p_age=11,p_name="Tom")
---需要指定所有的分區(qū)

刪除分區(qū)

--刪除分區(qū)
 alter table tab_test drop if exists partition(p_age=10); 
--刪除分區(qū)時(shí)，可以只指定局部 
alter table tab_test drop partition(p_name='Tom'); 

--動(dòng)態(tài)分區(qū)；動(dòng)態(tài)分區(qū)匹配最后選出的字段；只與字段順序有關(guān)系，與名字無關(guān)；同時(shí)存在靜態(tài)和動(dòng)態(tài)分區(qū)，動(dòng)態(tài)分區(qū)必須在靜態(tài)分區(qū)之后

insert into table tab_test partition(p_age,p_name)(
select name,
       age,
       num1,
       num2,
       msg,
       age as pppp_age,  --取不取別名都可以;分區(qū)需要出現(xiàn)在select出來的字段的最后位置，為了匹配。
       name as p_name   --寫個(gè)對(duì)應(yīng)的別名，看上去好理解一點(diǎn)
from tab_test_temp);

 CREATE EXTERNAL TABLE IF NOT EXISTS xxxx(
  prod_line_code string COMMENT 'xx',
  prod_line string COMMENT 'xx',
  sss bigint COMMENT 'xx'
) partitioned by (statisc_date string) STORED AS parquet
LOCATION '/x/u';
INSERT
  OVERWRITE TABLE xxxx partition(statisc_date) (
    select
      '' as prod_line_no,
      '' as prod_line,
      0 as sss,
      '2020-01-01' as statisc_date
    from
      yyyyyy t
  )

Hive -- 保留小數(shù)位數(shù)

方法一

select round(645342.875645342,2);
+------------+--+
|    _c0     |
+------------+--+
| 645342.88  |
+------------+--+

方法二

select cast(645342.875645342 as decimal(10,2))
. . . . . . . . . . . . . . . . . . . .> ;
+------------+--+
|    _c0     |
+------------+--+
| 645342.88  |
+------------+--+