命令行查看hbase端口 phoenix命令行查看hbase表

转载

mob64ca140bbb8b 2023-12-05 23:58:58

文章标签 命令行查看hbase端口 hive phoenix 双引号微信 文章分类 Hbase 数据库

1.Phoenix链接hbase sqlline.py cdh01,cdh02,cdh03 sqlline.py hadoop01,hadoop02,hadoop03,hadoop04,hadoop05
相关操作语法

按天数取每天的最大
select parkid,parkname,malenum,mennum,timestateid, DATE_FORMAT(from_unixtime(left(timestateid,10)),’%Y-%m-%d’) as dt from park_user) a
where timestateid between 1569859200000 and 1570464000000
group by parkid,dt

1，进入(cdh01是主机名，2181是zookeeper的端口)
sqlline.py cdh01:2181

2，退出（注意结尾不加分号）
！quit

3，查询所有表（注意结尾不加分号）
！tables

4，创建表（除了上面带感叹号的语句，其余语句都得加分号）

（1）表名和列族名以及表名如果需要小写都得加双引号。
create table if not exists “person” (“id” integer not null primary key, “cf”.“name” varchar, “cf”.“age” integer);
（2）这样创建出来的列族名称和列名都是大写的。
create table “person” (id integer not null primary key, cf.name varchar, cf.age integer);
注意：表名和列族名区分大小写，加了双引号就必须用小写表名去查否则查不到。列名不区分大小写，显示是大写，用小写依然查得到。

5，删除表结构
drop table “person”;

6，修改表结构
alter table “person” add sex varchar;

7，查看表结构信息
!describe “person”

8，创建表某一列索引
create index “person_index” on “person”(“cf”.“name”);

9，删除索引
drop index “person_index” on “person”

8，向表中插入一条数据（注意：数据值需要用引号时只能用单引号，双引号会报错）
upsert into “person” values(1,‘zhangsan’,27,‘男’);

9，删除表中数据（注意：数据值需要用引号时只能用单引号，双引号会报错）
delete from “person” where name=‘zhangsan’;

10，修改表中数据（注意：修改时必须带上id，否则会报错）（注意：数据值需要用引号时只能用单引号，双引号会报错）
upsert into “person”(id,sex) values(1, ‘女’);

11，查询表中数据（注意：数据值需要用引号时只能用单引号，双引号会报错）
（1）全表查询
select * from “person”;
（2）条件查询
select * from “person” where name=‘zhangsan’;
（3）group by
select sex ,count(sex) as num from “person” where age >20 group by sex;
(4)case when
select (case name when ‘zhangsan’ then ‘sansan’ when ‘lisi’ then ‘sisi’ else name end)as showname from “person”;

12.创建与hbase命名空间对应的schema CREATE SCHEMA IF NOT EXISTS “binlog”;

13.创建联合索引并带出需要的字段
CREATE INDEX ticket_total_data_uindex ON “ft_test_v2”.“ticket_total_data”(“timestamp”,“parkId”) include (“ticketClassName”,“ticketNum”,“ticketSales”);

14.删除索引
drop index park_in_index on “FT_v1”.“park_in”;
drop index PARK_GROUP_INDEX on “streaming_data”.“park_group”;

15.查询执行计划
explain select * from “user_info”.“user_info_basic” where “phoneNumber” = ‘15080560398’;

16.关联hbase中已经存在的表
create view “test”(id varchar not null primary key, “cf1”.“name” varchar, “cf1”.“age” varchar, “cf1”.“sex” varchar);
注意：
（1）如果不加列族会报错如下：
Error: ERROR 505 (42000): Table is read only. (state=42000,code=505)
（2）如果不加双引号则会匹配不到hbase表中的字段，结果就是虽然关联上数据库但是没有值！！！
（3）关联的时候，Phoenix建表最好都是varchar类型，不容易出错
（4）最好创建view视图，不要创建table表格。因为Phoenix端删除table会连带删除hbase表格，如果是view则不会。

17.拼接字符串 || 字符串拼装 (‘a’||‘b’) as str 结果 ab
create table if not exists “use_users” (
info.Age integer,
info.PhoneOperators varchar,
info.IDCardType integer,
info.executetime varchar
);

create table “person” (“id” integer not null primary key, “cf”.“name” varchar, “cf”.“age” integer);

alter table “person” add sex varchar;

//子查询1 某数据在某表第多少排名
select count(1) from “TICKET_STREAM”.“CENTER_SALE_PARK” t where t.“sellMoney” >= (select “sellMoney” from “TICKET_STREAM”.“CENTER_SALE_PARK” where “parkId” = 1008) ;
select t.“parkId”,t.“parkName”,t.“sellMoney”,t.“salePrecent” from “TICKET_STREAM”.“CENTER_SALE_PARK” t where t.“sellMoney” >= (select “sellMoney” from “TICKET_STREAM”.“CENTER_SALE_PARK” where “parkId” = 1000) ;

//子查询2
select d.“parkId”,e.“parkId”,e.“sellMoney”,d.“personNum” from “TICKET_STREAM”.“CENTER_SALE_PARK” e JOIN “TICKET_STREAM”.“CENTER_ENTER_PARK” d ON e.“parkId” = d.“parkId”;
//order by排序时遇到分组，先分组，再在分组的结果后排序
select * from ( select id,sum(money) as totalNum from table group by id ) t order by t.totalNum desc;

upsert into “streaming_data”.“ticket_sale”(“parkId”,“parkName”,“sellMoney”,“salePrecent”) values(1002, ‘世界’ , 895.1715, ‘16.7%’);
upsert into “TICKET_STREAM”.“CENTER_ENTER_PARK”(“parkId”,“parkName”,“personNum”,“personPrecent”) values(1002, ‘世界’ , 239, '12.7%);

upsert into “streaming_data”.“park_origin_divide_statement”(“uniqueId”,“parkId”,“origin”,“oneRange_in”,“oneRange_un”,“twoRange_in”,“twoRange_un”,“threeRange_in”,“threeRange_un”,“fourRange_in”,“fourRange_un”,“fiveRange_in”,“fiveRange_un”,“sixRange_in”,“sixRange_un”,“sevenRange_in”,“sevenRange_un”,“eightRange_in”,“eightRange_un”,“nineRange_in”,“nineRange_un”,“tenRange_in”,“tenRange_un”,“elevenRange_in”,“elevenRange_un”,“twelveRange_in”,“twelveRange_un”,“thirteenRange_in”,“thirteenRange_un”) values(1,1010,‘dianshang’,55,5,75,6,77,7,123,12,55,5,66,6,77,7,123,12,555,5,66,61,77,7,66,6,77,7);

upsert into “streaming_data”.“park_origin_divide_statement”(“uniqueId”,“parkId”,“origin”,“oneRange_in”,“oneRange_un”,“twoRange_in”,“twoRange_un”,“threeRange_in”,“threeRange_un”,“fourRange_in”,“fourRange_un”,“fiveRange_in”,“fiveRange_un”,“sixRange_in”,“sixRange_un”,“sevenRange_in”,“sevenRange_un”,“eightRange_in”,“eightRange_un”,“nineRange_in”,“nineRange_un”,“tenRange_in”,“tenRange_un”,“elevenRange_in”,“elevenRange_un”,“twelveRange_in”,“twelveRange_un”,“thirteenRange_in”,“thirteenRange_un”) values(2,1010,‘travle’,55,5,66,6,77,7,123,12,888,5,66,6,77,57,123,12,55,5,66,56,77,7,66,16,77,7);

upsert into “streaming_data”.“park_user”(“parkId”,“parkName”,“maleNum”,“menNum”,“malePercent”,“menPercent”,“oneRange”,“twoRange”,“threeRange”,“fourRange”,“fiveRange”,“sixRange”) values(1010, ‘世界’, 65, 35, ‘65%’, ‘35%’, 20,25,22,15,8,10);

select e.“parkId”,e.“sellMoney”,d.“saleAmount” from “streaming_data”.“ticket_sale” e JOIN “streaming_data”.“shop_sale” d ON e.“parkId” = d.“parkId” where e.“parkId” IN (1006, 1007, 1008, 1009);

,(select sum(a.“sellMoney” + b.“saleAmount”) as “totalNum” from “streaming_data”.“ticket_sale” a,“streaming_data”.“shop_sale” b) as f
,(select sum(a.“sellMoney”) as sellMoney from “streaming_data”.“ticket_sale” a ) f ,(select sum(b.“saleAmount”) as saleAmount from “streaming_data”.“shop_sale” b ) g

1.parkId primary key,parkName(公园名称),maleNum(人数),menNum(人数),malePercent,menPersent,oneRange(19岁以下)，twoRange(19_{30岁)，threeRange(31}38岁),fourRange(38以上)

//排序
select d.PARKID,d.SALEAMOUNT, (f.sellMoney + g.saleAmount) as totalNum from (select c.PARKID as PARKID, SUM(c.SALEAMOUNT) as SALEAMOUNT from ( select b."parkId" as parkId, b."saleAmount" as saleAmount from "streaming_data"."shop_sale" b union all select a."parkId" as parkId, a."sellMoney" as saleAmount from "streaming_data"."ticket_sale" a ) c GROUP BY c.PARKID ) d,(select sum(a."sellMoney") as sellMoney from "streaming_data"."ticket_sale" a ) f ,(select sum(b."saleAmount") as saleAmount from "streaming_data"."shop_sale" b ) g ORDER by d.SALEAMOUNT desc;

//按公园排序
SELECT d.PARKID AS PARKID,d.PARKNAME as PARKNAME, d.SALEAMOUNT AS SALEAMOUNT FROM (
SELECT c.PARKID AS PARKID,c.PARKNAME as PARKNAME, SUM(c.SALEAMOUNT) AS SALEAMOUNT FROM (
SELECT b.“parkId” AS parkId, b.“parkName” AS parkName, b.“saleAmount” AS saleAmount FROM “streaming_data”.“shop_sale” b WHERE b.“parkId” IN
(1007,1008, 1009,1010)
UNION ALL
SELECT a.“parkId” AS parkId, a.“parkName” AS parkName, a.“sellMoney” AS saleAmount FROM “streaming_data”.“ticket_sale” a WHERE a.“parkId” IN
(1007, 1008, 1009,1010)
) c GROUP BY c.PARKID, c.PARKNAME
) d ORDER BY d.SALEAMOUNT DESC;

//统计第几名
select count(1) from
(select c.PARKID as PARKID, SUM(c.SALEAMOUNT) as SALEAMOUNT from
( select b.“parkId” as parkId, b.“saleAmount” as saleAmount from “streaming_data”.“shop_sale” b union all select a.“parkId” as parkId,
a.“sellMoney” as saleAmount from “streaming_data”.“ticket_sale” a )
c GROUP BY c.PARKID) t1
where t1.SALEAMOUNT >=
(select t2.SALEAMOUNT from
(select c.PARKID as PARKID, SUM(c.SALEAMOUNT) as SALEAMOUNT from
( select b.“parkId” as parkId, b.“saleAmount” as saleAmount from “streaming_data”.“shop_sale” b union all select a.“parkId” as parkId,
a.“sellMoney” as saleAmount from “streaming_data”.“ticket_sale” a ) c
GROUP BY c.PARKID) t2
where t2.PARKID = 1008) ;
//查单表数据附带另一列值
select e.“parkId”,e.“sellMoney”,d.ss
from “streaming_data”.“ticket_sale” e,
(select sum(“saleAmount”) as ss from “streaming_data”.“shop_sale”) d
where e.“parkId” IN (1006, 1007, 1008, 1009);

----------------------------QAQ----------------------------------------------------

create schema IF NOT EXISTS “user_info”;

drop table “user_info”.“user_info_basic”;
create table if not exists “user_info”.“user_info_basic”(
“id” varchar not null PRIMARY KEY, --主键id
“info”.“headImg” varchar, --会员头像
“info”.“memberName” varchar, --会员姓名
“info”.“sex” varchar, --会员性别
“info”.“phoneNumber” varchar, --手机号码
“info”.“levelID” varchar, --会员等级
“info”.“birthday” varchar, --会员生日
“info”.“idCardNo” varchar, --证件号码
“info”.“career” varchar, – 职业
“info”.“isWechatVip” varchar, --是否绑定微信会员卡
“info”.“qq” varchar, --绑定qq号
“info”.“memberAddress” varchar, --会员常驻地
“info”.“memberStatus” varchar, --会员状态
“info”.“PlateNumber” varchar, --车牌号码
“info”.“age” INTEGER, --年龄
“info”.“userID” varchar, --唯一标识
“info”.“registerTime” varchar, --注册时间
“info”.“registerSource” varchar, --注册渠道
“info”.“upgradeTime” varchar, --升级时间
“info”.“upgradeSource” varchar --升级渠道
) SALT_BUCKETS=2, COMPRESSION=‘GZ’;

UPSERT INTO “user_info”.“user_info_basic” VALUES (‘101’,‘a’,‘小猪佩奇’,‘男’,‘13100000001’,‘绿卡’,‘2000-01-01’,‘0001’,‘学生’,‘是’,‘qq123456789’,‘北京’,‘已激活’,‘a123456’,2,‘a1’,‘2020-01-01’,‘微信’,‘2020-01-01’,‘旅游App’);
UPSERT INTO “user_info”.“user_info_basic” VALUES (‘102’,‘a’,‘小狗佩奇’,‘男’,‘13100000002’,‘绿卡’,‘2000-01-01’,‘0002’,‘学生’,‘是’,‘qq123456789’,‘北京’,‘已激活’,‘a123456’,2,‘a1’,‘2020-01-01’,‘微信’,‘2020-01-01’,‘旅游App’);
UPSERT INTO “user_info”.“user_info_basic” VALUES (‘103’,‘a’,‘小羊佩奇’,‘男’,‘13100000003’,‘绿卡’,‘2000-01-01’,‘0003’,‘学生’,‘是’,‘qq123456789’,‘北京’,‘已激活’,‘a123456’,2,‘a1’,‘2020-01-01’,‘微信’,‘2020-01-01’,‘旅游App’);
UPSERT INTO “user_info”.“user_info_basic” VALUES (‘104’,‘a’,‘小猫佩奇’,‘男’,‘13100000004’,‘绿卡’,‘2000-01-01’,‘0004’,‘学生’,‘是’,‘qq123456789’,‘北京’,‘已激活’,‘a123456’,2,‘a1’,‘2020-01-01’,‘微信’,‘2020-01-01’,‘旅游App’);
UPSERT INTO “user_info”.“user_info_basic” VALUES (‘105’,‘a’,‘小鸡佩奇’,‘男’,‘13100000005’,‘绿卡’,‘2000-01-01’,‘0005’,‘学生’,‘是’,‘qq123456789’,‘北京’,‘已激活’,‘a123456’,2,‘a1’,‘2020-01-01’,‘微信’,‘2020-01-01’,‘旅游App’);
UPSERT INTO “user_info”.“user_info_basic” VALUES (‘106’,‘a’,‘小鼠佩奇’,‘男’,‘13100000006’,‘绿卡’,‘2000-01-01’,‘0006’,‘学生’,‘是’,‘qq123456789’,‘北京’,‘已激活’,‘a123456’,2,‘a1’,‘2020-01-01’,‘微信’,‘2020-01-01’,‘旅游App’);
UPSERT INTO “user_info”.“user_info_basic” VALUES (‘107’,‘a’,‘小牛佩奇’,‘男’,‘13100000007’,‘绿卡’,‘2000-01-01’,‘0007’,‘学生’,‘是’,‘qq123456789’,‘北京’,‘已激活’,‘a123456’,2,‘a1’,‘2020-01-01’,‘微信’,‘2020-01-01’,‘旅游App’);
UPSERT INTO “user_info”.“user_info_basic” VALUES (‘108’,‘a’,‘小猴佩奇’,‘男’,‘13100000008’,‘绿卡’,‘2000-01-01’,‘0008’,‘学生’,‘是’,‘qq123456789’,‘北京’,‘已激活’,‘a123456’,2,‘a1’,‘2020-01-01’,‘微信’,‘2020-01-01’,‘旅游App’);
UPSERT INTO “user_info”.“user_info_basic” VALUES (‘109’,‘a’,‘小兔佩奇’,‘男’,‘13100000009’,‘绿卡’,‘2000-01-01’,‘0009’,‘学生’,‘是’,‘qq123456789’,‘北京’,‘已激活’,‘a123456’,2,‘a1’,‘2020-01-01’,‘微信’,‘2020-01-01’,‘旅游App’);
UPSERT INTO “user_info”.“user_info_basic” VALUES (‘100’,‘a’,‘小鹿佩奇’,‘男’,‘13100000000’,‘绿卡’,‘2000-01-01’,‘0000’,‘学生’,‘是’,‘qq123456789’,‘北京’,‘已激活’,‘a123456’,2,‘a1’,‘2020-01-01’,‘微信’,‘2020-01-01’,‘旅游App’);

查询正常：select “registerTime”,“memberStatus” from “user_info”.“user_info_basic” where “id” =‘102’ order by “memberStatus” limit 3;
查询正常：select “registerTime”,“memberStatus”,“idCardNo” from “user_info”.“user_info_basic” where “idCardNo”=‘0002’ order by “memberStatus” limit 3;
查询异常：select “registerTime”,“memberStatus” from “user_info”.“user_info_basic” where “idCardNo”=‘0002’ order by “memberStatus” limit 3;

question ：为什么查询条件的非主键字段不在选择中字段中会出现异常？？？

2.全局索引示例
phoenix全局索引缺陷：写入慢、查询块

drop index user_info_basic_index on “user_info”.“user_info_basic”;
CREATE INDEX user_info_basic_index ON “user_info”.“user_info_basic”(“phoneNumber”, “idCardNo”) INCLUDE(“headImg”,“memberName”,“sex”,“levelID”,“birthday”,“career”,“isWechatVip”,“qq”,“memberAddress”,“memberStatus”,“PlateNumber”,“age”,“userID”,“registerTime”,“registerSource”,“upgradeTime”,“upgradeSource”);

命令行查看hbase端口 phoenix命令行查看hbase表_双引号