1.Phoenix链接hbase sqlline.py cdh01,cdh02,cdh03 sqlline.py hadoop01,hadoop02,hadoop03,hadoop04,hadoop05 相关操作语法 https://blog.csdn.net/u010916338/article/details/80309513
按天数取每天的最大 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,dt1，进入(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则不会。
原文：https://blog.csdn.net/u010916338/article/details/80309513
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(1930岁)，threeRange(3138岁),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”);
