hive复习草稿1，待整理

创建外部表并导入不同格式的数据到数据仓库的流程一般包括以下步骤：

创建外部表： 使用 CREATE EXTERNAL TABLE 语句创建外部表，定义表的结构和字段。外部表的定义应该与要导入的数据格式相匹配。

sqlCopy codeCREATE EXTERNAL TABLE IF NOT EXISTS my_external_table (
    column1 data_type,
    column2 data_type,
    ...
)
ROW FORMAT ...  -- 指定数据的序列化/反序列化器(SerDe)和其他格式属性
LOCATION '/your/hdfs/path/my_external_table';

这里的 ROW FORMAT 子句通常包含有关数据格式和序列化/反序列化器的信息。

准备数据： 确保外部数据源中的数据格式与外部表的定义相匹配。数据可以是各种格式，如文本文件、CSV、JSON、Parquet等。
将数据上传至指定路径： 如果数据还不在指定的 HDFS 路径中，需要将数据上传到指定的外部表路径。
导入数据： 使用 LOAD DATA 语句或其他适当的方式，将外部数据加载到外部表中。
1
2
sqlCopy code LOAD DATA INPATH '/your/local/or/hdfs/path/source_data' INTO TABLE my_external_table;
或者，如果使用的是外部表的某些特殊格式或序列化/反序列化器，可能需要使用相应的导入方式。
查询和分析： 通过 Hive 查询语句对外部表中的数据进行查询和分析。
1
2
sqlCopy code SELECT * FROM my_external_table WHERE ...;

这个流程确保了外部表能够引用外部数据源中的数据，而不需要将数据复制到 Hive 的仓库目录中。这对于处理大量外部数据、避免数据冗余以及与其他数据处理工具集成非常有用。

六、示例

数据格式: data.csv

"Name","Age","City"
"John",25,"New York"
"Alice",30,"San Francisco"
"Bob",22,"Los Angeles"

CREATE EXTERNAL TABLE IF NOT EXISTS example_table (
    -- 列定义
    Name STRING,
    Age INT,
    City STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    'header' = 'true'  -- 表示 CSV 文件包含表头
)
LOCATION '/your/hdfs/path/data.csv';

七、补充

/*create database yb12211;
use yb12211;
show tables;
use default;
use yb12211;
show tables;
drop table hive_ext_emp_basic;*/

create database yb12211;
use yb12211;
create external table if not exists hive_ext_emp_basic(
   emp_id int,
   emp_name string,
   job_title string,
   company string,
   start_date date,
   quit_date date
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties(
    'separatorChar'=',',
    'quoteChar'='"',
    'escapeChar'='\\'
)
stored as textfile
location '/hive_data/hive_cha01/emp_base'
tblproperties('skip.header.line.count'='1');

select *
from hive_ext_emp_basic limit 30;
----------------------------------------------------------------------------------
drop table if exists hive_ext_json_family;
create external table if not exists hive_ext_json_family(
    name string,
    age int,
    gender string,
    phone string
)

row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
stored as textfile
location '/hive_data/hive_cha01/json';

select *
from hive_ext_json_family;

drop table if exists hive_ext_regex_test1w;
create external table if not exists hive_ext_regex_test1w(
    id int,
    gender string,
    order_time timestamp,
    order_amount decimal(10,2)
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties (
    'input.regex'='(\\d+);(.*?);(\\d{4}-\\d{1,2}-\\d{1,2} \\d{1,2}:\\d{1,2}:\\d{1,2});(\\d+\.?\\d+?)'
    )
stored as textfile
location '/hive_data/hive_cha01/regex';

desc formatted hive_ext_regex_test1w;
create table if not exists hive_internal_par_regex_test1w(
    user_id int,
    user_gender string,
    order_time timestamp,
    order_amount decimal(10,2)
)
partitioned by(year int)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
    with serdeproperties(
    'input.regex'='(\\d+);(.*?);(\\d{4}-\\d{1,2}-\\d{1,2} \\d{1,2}:\\d{1,2}:\\d{1,2});(\\d+\.?\\d+?)'
);
desc hive_internal_par_regex_test1w;

load data local inpath '/root/test2012.log'
    overwrite into table hive_internal_par_regex_test1w partition(year=2012);
load data local inpath '/root/test2013.log'
    overwrite into table hive_internal_par_regex_test1w partition(year=2013);
select *from hive_internal_par_regex_test1w where year=2013 limit 10;

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;



-- set hive.vectorized.execution.enabled = true;
--
-- set hive.support.concurrency=false;


drop table if exists hive_internal_par_regex_test1w;
//分区
create table if not exists hive_internal_par_regex_test1w(
     user_id int,
     user_gender string,
     order_time timestamp,
     order_amount decimal(10,2)

)partitioned by (year int)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;

 insert into table hive_internal_par_regex_test1w partition(year)
select *,year(order_time) from hive_ext_regex_test1w where year(order_time)>=2014;


show partitions hive_internal_par_regex_test1w;

-----------------------------------------------------------------
drop table if exists employee;
create external table if not exists employee(
    name string,
    city string,
    detail struct<gender:string,age:int>,
    scores map<string,int>,
    position map<string,string>
)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
stored as textfile
location '/hive_data/hive_cha01/employee';

drop table if exists ctas_employee;
create table ctas_employee as select
*from employee;

drop table if exists cte_employee;
create table cte_employee as
    with cte
    as(select *
       from employee
       where name='Michael' or detail.gender="Female")
select *from cte;

alter table cte_employee rename to c_employee;

drop table if exists c_employee;
drop table if exists employee_partitioned;
create table if not exists employee_partitioned(
    name string,
    id int,
    telephone string

)
partitioned by(year int,month int)
row format delimited
fields terminated by '|'
lines terminated by '\n'
stored as textfile;

create table if not exists tmp_employee(
    name       string,
    id         int,
    telephone  string,
    order_time string
)row format delimited
fields terminated by '|';
load data local inpath '/root/data/employee_hr.txt' into table tmp_employee;

create table temp_emp as(
select
name,id,telephone,order_time,
substr(order_time,0,4) as year,substr(order_time,6,2) as month
from tmp_employee);

truncate table employee_partitioned;
insert into employee_partitioned partition (year='2014',month="12")
select name,id,telephone from temp_emp;

select *
from tmp_employee
where substr(order_time,0,4)="2014" and substr(order_time,6,2)="12")



alter table employee_partitioned add partition (year=2020,month=4);
alter table employee_partitioned add partition (year=2020,month=5);
alter table employee_partitioned drop partition (year=2020,month=4);
show partitions employee_partitioned;

set hive.exec.dynamic.partition.mode=nonstrict;

insert into table employee_partitioned partition(year,month)
select a.name,a.id,a.telephone,a.year,a.month from(select * from temp_emp where year='2014' and month='12')a;

select *
from employee_partitioned where year='2014' and month ='12';


set hive.enforce.bucketing = true;
-- Michael|100|Montreal,Toronto|Male,30|DB:80|Product:DeveloperLead
drop table if exists bucket_employee;
create table if not exists bucket_employee (
     name string,
     id int,
     city string,
     detail struct<gender:string,age:int>,
     scores map<string,int>,
     position map<string,string>

)
clustered by (id) into 4 buckets
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
stored as textfile;
load data local inpath '/root/data/employee_id.txt' into table bucket_employee;

desc bucket_employee;

show tables;

select id,gender,sum(order_amount) total
from hive_ext_regex_test1w
where order_time>=2016
group by gender, id
having total>=20000;
with
total_amount as(
    select sum(order_amount) total
    from hive_ext_regex_test1w
    where order_time>=2016
    group by gender, id
    having total>=20000
),
level_amount as (
    select round(total/10000) as level
    from total_amount
)
select level,count(*) as level_count
from level_amount
group by level;

drop table if exists cb_o_01;
drop table if exists cb_o_02;


create database yb12211_2;

use yb12211_2;

CREATE EXTERNAL TABLE IF NOT EXISTS cb_customers (
                                                     customer_id int,
                                                     customer_fname varchar(45),
                                                     customer_lname varchar(45),
                                                     customer_email varchar(45),
                                                     customer_password varchar(45),
                                                     customer_street varchar(255),
                                                     customer_city varchar(45),
                                                     customer_state varchar(45),
                                                     customer_zipcode varchar(45)
)
    ROW FORMAT  serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
        with serdeproperties (
        'separatorChar'=',',
        'quoteChar'='"',
        'escapeChar'='\\'
        )
    LOCATION '/hive_data/hive_cha02/customers';

CREATE EXTERNAL TABLE IF NOT EXISTS cb_categories (
                                                      category_id int,
                                                      category_department_id int,
                                                      category_name varchar(45)
)
    ROW FORMAT  serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
        with serdeproperties (
        'separatorChar'=',',
        'quoteChar'='"',
        'escapeChar'='\\'
        )
    LOCATION '/hive_data/hive_cha02/categories';

CREATE EXTERNAL TABLE IF NOT EXISTS cb_departments (
                                                       department_id int,
                                                       department_name varchar(45)
)
    ROW FORMAT  serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
        with serdeproperties (
        'separatorChar'=',',
        'quoteChar'='"',
        'escapeChar'='\\'
        )
    LOCATION '/hive_data/hive_cha02/departments';

CREATE EXTERNAL TABLE IF NOT EXISTS cb_order_items (
                                                       order_item_id int,
                                                       order_item_order_id int,
                                                       order_item_product_id int,
                                                       order_item_quantity int,
                                                       order_item_subtotal float,
                                                       order_item_product_price float)
    ROW FORMAT  serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
        with serdeproperties (
        'separatorChar'=',',
        'quoteChar'='"',
        'escapeChar'='\\'
        )
    LOCATION '/hive_data/hive_cha02/order_items';

CREATE EXTERNAL TABLE IF NOT EXISTS cb_orders (
                                                  order_id int,
                                                  order_date date,
                                                  order_customer_id int,
                                                  order_status varchar(45)
)
    ROW FORMAT  serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
        with serdeproperties (
        'separatorChar'=',',
        'quoteChar'='"',
        'escapeChar'='\\'
        )
    LOCATION '/hive_data/hive_cha02/orders';

CREATE EXTERNAL TABLE IF NOT EXISTS cb_products (
                                                    product_id int,
                                                    product_category_id int,
                                                    product_name varchar(45),
                                                    product_description varchar(255),
                                                    product_price float,
                                                    product_image varchar(255))
    ROW FORMAT  serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
        with serdeproperties (
        'separatorChar'=',',
        'quoteChar'='"',
        'escapeChar'='\\'
        )
    LOCATION '/hive_data/hive_cha02/products';


create table tmp_cb_order_ymbsc as
select
    year(O.order_date) as year,
    month(O.order_date) as month,
       C.category_department_id as dept_id,
       C.category_id as cate_id,
       P.product_id as prod_id,
       sum(I.order_item_quantity) as quantity,
       sum(I.order_item_quantity*P.product_price) as amount
from cb_order_items as I
inner join  cb_orders as O
on o.order_id = I.order_item_order_id
 and O.order_status = 'COMPLETE'
inner join cb_products as P
on P.product_id = I.order_item_product_id
inner join cb_categories as C
on C.category_id = P.product_category_id
group by
    year(O.order_date),
    month(O.order_date),
       C.category_department_id,
       C.category_id,
       P.product_id;

show tables;

set mapreduce.map.cpu.vcores=1;
set mapreduce.reduce.cpu.vcores=1;
with cb_order_quantity_by_product as(
    select prod_id,sum(quantity) as quantity
    from tmp_cb_order_ymbsc
    group by prod_id
),cb_order_quantity_by_product_rnk as (
    select
        prod_id, quantity,dense_rank() over (order by quantity desc ) as rnk
    from cb_order_quantity_by_product
)
select *
from cb_order_quantity_by_product_rnk
where rnk<=10;

select order_id,order_status,order_date,
        ntile(4) over(partition by  year(order_date)) as nt_no
from cb_orders;

show tables;

with user_total_amount as (
    select id, cast(sum(order_amount) as bigint) as sum_amount
    from hive_ext_regex_test1w
    where id is not null
    group by id
)
     -- select percentile(sum_amount,0.5)
select percentile(sum_amount,array(0.3,0.6,0.9)) as pt3
from user_total_amount;

with tmp as (
    select json_tuple('{"name":"张三","hobbies":["beauty","money","power"],"address":{"province":"江苏","city":"南京"}}','name','hobbies','address')as(name,hobbies,address)
),tmp2 as(
    select name,
--         get_json_object(address,'$.province') as province,
--         get_json_object(address,'$.city') as city,
           //正则的分组提取 '.....(1).....(2)' 0 表示整个字符串 1表示第一组
           regexp_extract(address,'\\{"province":"(.*?)","city":"(.*?)"\\}',1) as province,
           regexp_extract(address,'\\{"province":"(.*?)","city":"(.*?)"\\}',2) as city,
        regexp_replace(hobbies,'\\[|]|"','') as hobbies
    from tmp
)select * from tmp2
where locate('beauty',hobbies)>0;
-- where hobby rlike '.*beauty.*';


//正则分割
select split('1,2,3,4',',');

select split('ab?cd_ef', '\\?|\\_')[2];
select parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b',
    'PROTOCOL');
select parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b',
    'HOST');

select parse_url('https://search.jd.com/' ||
                 'Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&' ||
                 'enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|' ||
                 '&wq=%E5%8D%8E%E4%B8%BA&' ||
                 'pvid=65c357d9dfb44555a9eb8708ca539b8b',
                 'QUERY','keyword');

select reflect('java.net.URLDecoder','decode','%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60','UTF-8');
-- 在hive中调用Java类的方法 输出华为手机mate60

select parse_url_tuple('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b
','HOST','QUERY') as (host,query);

with tmp as (
    select
    parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b',
        'QUERY','keyword')as keyword,
    parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b',
        'QUERY','enc')as enc
)select reflect('java.net.URLDecoder','decode',keyword,enc) as keyword from tmp;

-- select xpath('<student stuId = "1"><name>henry</name><age>22</age><gender>male</gender></student>',
--     'student/@stuId');

select xpath_int('<student stuId = "1"><name>henry</name><age>22</age><gender>male</gender></student>',
             'student/age/text()');

-- array(List:collet:list(F),collet_set(F))

select customer_lname,collect_list(customer_fname) as fanme
from cb_customers
group by customer_lname;
-- map
select `array`(`array`('henry','jack'),`array`('pola','rose'));

select split('a,b,c',',');

select size(split('a,b,c',','));

select array_contains(split('a,b,c',','),'a');

select split('a,b,c',',')[0];

select sort_array(`array`(31,43,1,24,45,3,46,32));


select struct('henry',22,true);


select cast('100.9'as int );
//构造结构体
select named_struct('name','henry','age',22,'is_member',false);

select  `map`("java",88,'hadoop',59,'hive',56);

select str_to_map('java:88,hadoop:88,hive:100',',',":");

select map_keys(`map`("java",88,'hadoop',59,'hive',56)) as key_set;

select map_values(`map`("java",88,'hadoop',59,'hive',56)) as map_values;

select array_contains(map_keys(`map`("java",88,'hadoop',59,'hive',56)),'hadoop') as has_hadoop;

//只有struct 能用sort_array_by
select sort_array_by(`array`(
    named_struct('name','henry','age',22,'is_member',true),
    named_struct('name','jerry','age',25,'is_member',true),
    named_struct('name','tom','age',25,'is_member',false)
    ),'age');


select sort_array_by(`array`(
                             named_struct('name','henry','age',22),
                             named_struct('name','jerry','age',25),
                             named_struct('name','tom','age',25)
                         ),'age');
select stack(5,88,22,33,66,99);

select if(false,'2','8');

select in_file('tom','/root/data/hive_func.data');

select isfalse(false);
select istrue(false);
select nullif(2,null);

select nvl(null,2);

select coalesce(null,2,3);

select version();

select posexplode(`array`("aa","bb","cc"));

select inline(`array`(
    named_struct('name','henry','age',20),
    named_struct('name','jack','age',40),
    named_struct('name','tom','age',16)
    ));

with tmp as (
    select named_struct('name','me','age',18,'is_member',true) as self,
           `array`(
                   named_struct('name','henry','age',20,'is_member',true),
                   named_struct('name','jack','age',40,'is_member',false),
                   named_struct('name','tom','age',16,'is_member',true)
               )as arr_struct
        )
select self.name,self,age,self.is_member,name ,age,is_member
from tmp
lateral view inline(arr_struct)V as name,age,is_member;

select version();



select isnull(null);
select nvl(null,1);

select case  2 when 1 then 1  else 2 end ;

select coalesce(null,null,1);

select name,
case year when 2011 then 1 else 0 end numsOf2011
from temp_emp;

select abs(-20.5);

select positive(8);

select date('2020-05-21 02:40:00');

select date('2020-99-99');

select to_date('2022-1-1 10');

select unix_timestamp('20221112 20:20:16','yyyyMMdd HH:mm:ss');



select  date_format('2022-1-1 20:25:15','yyyy/MM/dd HH');

select from_unixtime(19465465);

select unix_timestamp("2022-01-05 21:18:24");

select concat('11','22');

select concat_ws(`array`(',',':')[0],'21',`array`('11','22','33'));




select concat_ws(',','aa','bb',`array`('cc','dd'));

select context_ngrams(1=2,`array`('22','44','66',2,2));

select ascii('ab');

select base64('100');

select binary('test');

select base64(binary('test'));

select format_number(1221.121,2);

select substr('abcde',3);

select instr('abcde','cde');

select length('abc');

select locate('bc','abcbcdebc',5);

select printf("");

select printf('name : %s, id : %d',name,id)
from temp_emp;

select str_to_map('a/d&b/h&c/f&e/t','&','/');

select lower('qfSSssSdd');

select trim(' ab  v e ');


select regexp_replace('aa11vv4+4nn22','\\d{2}','&');

select size(`map`('Chinese',100,'Math',20));

select size(`array`('Chinese','Math','English'));

select sort_array_by(`array`(
                             named_struct('name','henry','age',22),
                             named_struct('name','jerry','age',25),
                             named_struct('name','tom','age',27)
                         ),'age','desc');

select array_contains(`array`('11','22','33'),'11')
;


select sort_array(`array`(22,33,11));
select sort_array(`array`(
        named_struct('name','zebra','age',22),
        named_struct('name','ant','age',22),
        named_struct('name','tom','age',26),
        named_struct('name','cat','age',26)
    ));

select map_keys(`map`('Chinese',100,'Math',99));
select map_values(`map`('Chinese',100,'Math',99));

select explode(`map`('Chinese',100,'Math',99));

select explode(`array`(
        named_struct('name','zebra','age',22),
        named_struct('name','ant','age',22),
        named_struct('name','tom','age',26),
        named_struct('name','cat','age',26)
    ));

select str_to_map('a/d,b/h,c/f,e/t',',','/');

select split('abc11def','\\d+')[0];



set mapreduce.map.cpu.vcores;

大数据 > hive

#bigData #hive #sql

hive复习草稿1，待整理

https://leaf-domain.gitee.io/2024/01/25/hiveTableEmp/

作者

叶域

发布于

2024年1月25日

许可协议

hive复习草稿2，待整理上一篇

Hive创建外部表导入不同格式的数据至数据仓库下一篇