sql---5

 

1.hbase的rowkey    
select city_no cityno,dept_no deptno,fila_no filano,group_no groupno,mach_no machno,line_no lineno,bus_no busno,
is_up_down isupdown,label_no labelno,up_passenger uppassenger,down_passenger downpassenger,site_time sitetime,ins_time instime,
is_trans istrans,mark,station_full_rate stationfullrate,total_people_num totalpeoplenum,
lpad(bus_no,6,0)||reverse(to_char(site_time,'yyyymmddhh24miss')) rowkey
from ***his
where site_time<trunc(sysdate);

--------------------------------------
2.视图
create or replace view v_****031 as
select line_no lineno,is_up_down isupdown,label_no labelno,station_id stationid,station_name stationname,
allot_time ALLOTTIME,hour,up_passenger UPPASSENGER,down_passenger DOWNPASSENGER,
--=line_no||is_up_down||allot_time||hour||station_id rowkey
reverse(allot_time)||hour||is_up_down||line_no rowkey
from *******24268;
3.视图
-------------------------------------------------------
create or replace view v_****n033 as
select line_no lineno,is_up_down isupdown,label_no labelno,station_id stationid,station_name stationname,
allot_time ALLOTTIME,hour,up_passenger UPPASSENGER,down_passenger DOWNPASSENGER,
--=line_no||is_up_down||allot_time||hour||station_id rowkey
reverse(allot_time||hour)||is_up_down||line_no||station_id rowkey
from ****68;

 2.hive查看表的详细信息（表的结构，可以生成数据）

show create table h_tmbuspassengerupdown;

3.sqoop导入数据

[hdfs@tm-cdh-15 ~]$ ./sqoop import -D sqoop.hbase.add.row.key=true --connect jdbc:oracle:thin:@192.*****.8:1521/orclgps --username hadoop --password hadoop --table V_****N033  --hbase-table k***wn033 --hbase-row-key ROWKEY --column-family cf1 -m 1

 4.sqoop优化

 sqoop import -D sqoop.hbase.add.row.key=true --connect jdbc:oracle:thin:@192.***:1521/orclgps --username hadoop --password hadoop --table K**3  --hbase-table k**_temp03  
--hbase-row-key ROWKEY --column-family cf1 -m 6 --split-by ROWKEY

package Test05;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.Cell;
import org.apache.hadoop.hbase.CellUtil;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.TableName;
import org.apache.hadoop.hbase.client.*;
import org.apache.hadoop.hbase.coprocessor.BaseRegionObserver;
import org.apache.hadoop.hbase.coprocessor.ObserverContext;
import org.apache.hadoop.hbase.coprocessor.RegionCoprocessorEnvironment;
import org.apache.hadoop.hbase.regionserver.wal.WALEdit;
import org.apache.hadoop.hbase.util.Bytes;
import java.io.IOException;
import java.util.List;

@SuppressWarnings("deprecation")
public class PutObserver05 extends BaseRegionObserver {
    private static HBaseConfiguration cfg = null;
    static {
        Configuration HBASE_CONFIG = new Configuration();
        HBASE_CONFIG.set("hbase.zookeeper.quorum", "192.168.*.1*");
        HBASE_CONFIG.set("hbase.zookeeper.property.clientPort", "2181");
        cfg = new HBaseConfiguration(HBASE_CONFIG);
    }

    @Override
    public void postPut(ObserverContext<RegionCoprocessorEnvironment> e,
                        Put put, WALEdit edit, Durability durability) throws IOException {
        // 获取二级索引表
//        HTableInterface table = e.getEnvironment().getTable(TableName.valueOf("kkk03"));
//        byte[] rowkey = put.getRow();

        // Create a connection to the cluster.
        HConnection connection = HConnectionManager.createConnection(cfg);
        HTableInterface ctable = connection.getTable(TableName.valueOf("k#03"));
        byte[] rowkey = put.getRow();

        // 获取值
        List<Cell> cellList1 = put.get(Bytes.toBytes("cf1"), Bytes.toBytes("LINENO"));
        List<Cell> cellList2 = put.get(Bytes.toBytes("cf1"), Bytes.toBytes("ISUPDOWN"));

        List<Cell> cellList3 = put.get(Bytes.toBytes("cf1"), Bytes.toBytes("ROWKEY"));
        List<Cell> cellList4 = put.get(Bytes.toBytes("cf1"), Bytes.toBytes("STATIONID"));

        byte[] val1 = CellUtil.cloneValue(cellList1.get(0));
        byte[] val2 = CellUtil.cloneValue(cellList2.get(0));
        byte[] val3 = CellUtil.cloneValue(cellList3.get(0));
        byte[] val4 = CellUtil.cloneValue(cellList4.get(0));

        String v1 = new String(val1, "utf-8");
        String v2 = new String(val2, "utf-8");
        String v3 = new String(val3, "utf-8");  //-----
        String v4 = new String(val4, "utf-8");  //-----

        String vv = v3.substring(0, 10);
        String cl = v1 + v2 + v4 + vv;

        // 将数据插入二级索引表
        //for (Cell cell1 : cellList1) {
        // 列info:name的值作为二级索引表的rowkey
        Put indexPut = new Put(cl.getBytes("UTF-8"));
        // 列info:score的值作为二级索引表中列info:score的值
        indexPut.add(Bytes.toBytes("cf1"), Bytes.toBytes("rowkey"), rowkey);
        // 数据插入二级索引表
        ctable.put(indexPut);
        ctable.close();
        //
        ctable.close();
        connection.close();
    }

}

 1.统计站序的最大位数

select distinct LABEL_NO from T*_ANA_STA_***_HIS order by LABEL_NO desc

 2.优化rowkey设计

 create table ***09  as
select line_no lineno,is_up_down isupdown,label_no labelno,station_id stationid,station_name stationname,
up_passenger UPPASSENGER,down_passenger DOWNPASSENGER,ins_time instime,
--=line_no||is_up_down||allot_time||hour||station_id rowkey
reverse(to_char(ins_time,'yyyymmdd'))|| is_up_down||lpad(line_no,4,0)||to_char(ins_time,'hh24miss')|| lpad(label_no,2,0) rowkey
from *****S where rownum <= 10;