Apache Hbase 系列文章
1、hbase-2.1.0介绍及分布式集群部署、HA集群部署、验证、硬件配置推荐 2、hbase-2.1.0 shell基本操作详解 3、HBase的java API基本操作(创建、删除表以及对数据的添加、删除、查询以及多条件查询) 4、HBase使用(namespace、数据分区、rowkey设计、原生api访问hbase) 5、Apache Phoenix(5.0.0-5.1.2) 介绍及部署、使用(基本使用、综合使用、二级索引示例)、数据分区示例 6、Base批量装载——Bulk load(示例一:基本使用示例) 7、Base批量装载-Bulk load(示例二:写千万级数据-mysql数据以ORCFile写入hdfs,然后导入hbase) 8、HBase批量装载-Bulk load(示例三:写千万级数据-mysql数据直接写成Hbase需要的数据,然后导入hbase)
(文章目录)
本文主要介绍Bulk load的用法,即MapReduce和bulk load的配合使用。 本文是介绍Bulk load用法的系列的第一篇,后面还会有2篇介绍Bulk load的使用。 本文前提依赖hbase可用、phoenix可用。 本文分为2个部分,即Bulk load介绍和具体使用示例。
示例一:50万数据,介绍bulk load的基本用法,包含mapper的写法、bulk load的导入、phoenix的客户端视图用法
一、 HBase批量装载——Bulk load
1、简介
将外部的数据导入到HBase集群中,例如:将一些历史的数据导入到HBase做备份。
可以通过HBase的Java API put方式可以将数据写入到HBase中,也可以通过MapReduce编写代码将HDFS中的数据导入到HBase。但这些方式都是基于HBase的原生API方式进行操作的。这些方式有一个共同点,就是需要与HBase连接,然后进行操作。
HBase服务器要维护、管理这些连接,以及接受来自客户端的操作,会给HBase的存储、计算、网络资源造成较大消耗。
在需要将海量数据写入到HBase时,通过Bulk load(大容量加载)的方式,会变得更高效。可以这么说,进行大量数据操作,Bulk load是必不可少的。
HBase的数据最终是需要持久化到HDFS。HDFS是一个文件系统,那么数据可定是以一定的格式存储到里面的。例如:Hive我们可以以ORC、Parquet等方式存储。
HBase有自己的数据格式,那就是HFile。Bulk Load就是直接将数据写入到StoreFile(HFile)中,从而绕开与HBase的交互,HFile生成后,直接一次性建立与HBase的关联即可。使用BulkLoad,绕过了Write to WAL,Write to MemStore及Flush to disk的过程。
更多可以参考官方对Bulk load的描述:https://hbase.apache.org/book.html#arch.bulk.load
2、Bulk load MapReduce程序开发
Bulk load的流程主要分为两步:
- 通过MapReduce准备好数据文件(Store Files)
- 加载数据文件到HBase
二、示例
本示例就是通过MapReduce生产测试数据,然后将数据导入到hbase中。
MapReduce程序,只有Mapper,没有Reducer(因为不需要处理)。
数据结构如下
1、创建表
#namespace非必需
create_namespace "BANK_BU"
#建表,指定压缩方式、预分区方式
create "BANK_BU:TRANSFER_RECORD", { NAME => "C1", COMPRESSION => "GZ"}, { NUMREGIONS => 6, SPLITALGO => "HexStringSplit"}
hbase(main):013:0> create_namespace "BANK_BU"
Took 0.7781 seconds
hbase(main):014:0> create "BANK_BU:TRANSFER_RECORD", { NAME => "C1", COMPRESSION => "GZ"}, { NUMREGIONS => 6, SPLITALGO => "HexStringSplit"}
Created table BANK_BU:TRANSFER_RECORD
Took 2.3684 seconds
=> Hbase::Table - BANK_BU:TRANSFER_RECORD
hbase(main):015:0> list
TABLE
BANK_BU:TRANSFER_RECORD
SYSTEM:CATALOG
SYSTEM:FUNCTION
SYSTEM:LOG
SYSTEM:MUTEX
SYSTEM:SEQUENCE
SYSTEM:STATS
WB
8 row(s)
Took 0.0148 seconds
=> ["BANK_BU:TRANSFER_RECORD", "SYSTEM:CATALOG", "SYSTEM:FUNCTION", "SYSTEM:LOG", "SYSTEM:MUTEX", "SYSTEM:SEQUENCE", "SYSTEM:STATS", "WB"]
2、编码
1)、pom.xml
<?xml version="1.0"?>
<project
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"
xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>com.okcard</groupId>
<artifactId>bigdata-component</artifactId>
<version>0.0.1-SNAPSHOT</version>
</parent>
<groupId>com.okcard</groupId>
<artifactId>hbase</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>hbase</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<repositories><!-- 代码库 -->
<repository>
<id>aliyun</id>
<url>http://maven.aliyun.com/nexus/content/groups/public/</url>
<releases>
<enabled>true</enabled>
</releases>
<snapshots>
<enabled>false</enabled>
<updatePolicy>never</updatePolicy>
</snapshots>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-client</artifactId>
<version>2.1.0</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>
<!-- hadoop的通用包 -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.7.5</version>
</dependency>
<!-- Xml操作相关 -->
<dependency>
<groupId>com.github.cloudecho</groupId>
<artifactId>xmlbean</artifactId>
<version>1.5.5</version>
</dependency>
<!-- 操作Office库 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
<!-- 操作JSON -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.62</version>
</dependency>
<!-- phoenix core -->
<dependency>
<groupId>org.apache.phoenix</groupId>
<artifactId>phoenix-core</artifactId>
<version>5.0.0-HBase-2.0</version>
<!-- 解决打包 Failure to find org.glassfish:javax.el:pom:3.0.1-b08-SNAPSHOT in xxx -->
<exclusions>
<exclusion>
<groupId>org.glassfish</groupId>
<artifactId>javax.el</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- phoenix 客户端 -->
<dependency>
<groupId>org.apache.phoenix</groupId>
<artifactId>phoenix-queryserver-client</artifactId>
<version>5.0.0-HBase-2.0</version>
<!-- 解决打包 Failure to find org.glassfish:javax.el:pom:3.0.1-b08-SNAPSHOT in xxx -->
<exclusions>
<exclusion>
<groupId>org.glassfish</groupId>
<artifactId>javax.el</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- HBase对mapreduce的支持 -->
<dependency>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-mapreduce</artifactId>
<version>2.1.0</version>
</dependency>
<!-- hadoop mr任务客户端 -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-mapreduce-client-jobclient</artifactId>
<version>2.7.5</version>
</dependency>
<!-- 客户端 -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-mapreduce-client-core</artifactId>
<version>2.7.5</version>
</dependency>
<!-- hadoop权限认证相关 -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-auth</artifactId>
<version>2.7.5</version>
</dependency>
<!-- 方便操作文件apache的工具类包 -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>
<dependency>
<groupId>org.apache.htrace</groupId>
<artifactId>htrace-core</artifactId>
<version>3.2.0-incubating</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>2.5.6</version>
</dependency>
<dependency>
<groupId>jdk.tools</groupId>
<artifactId>jdk.tools</artifactId>
<version>1.8</version>
<scope>system</scope>
<systemPath>${JAVA_HOME}/lib/tools.jar</systemPath>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.1</version>
<configuration>
<target>1.8</target>
<source>1.8</source>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-shade-plugin</artifactId>
<version>3.1.1</version>
<executions>
<execution>
<phase>package</phase>
<goals>
<goal>shade</goal>
</goals>
<configuration>
<artifactSet>
<excludes>
<exclude>org.apache.htrace:htrace-core:3.2.0-incubating</exclude>
</excludes>
</artifactSet>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
2)、java bean
package org.hbase.bank;
import lombok.Data;
@Data
public class TransferRecord {
private String id;
private String code;
private String rec_account;
private String rec_bank_name;
private String rec_name;
private String pay_account;
private String pay_name;
private String pay_comments;
private String pay_channel;
private String pay_way;
private String status;
private String timestamp;
private String money;
}
3)、mapper 和 driver
1、Mapper
HBase提供了两个类来专门对MapReduce支持: ImmutableBytesWritable:对应rowkey MapReduceExtendedCell:对应 列 → 值(键值对)
- 实现步骤
- 创建一个BankRecordMapper的类继承Mapper类, Mapper的泛型为 a)输入key:LongWritable b)输入value:Text c)输出key:ImmutableBytesWritable d)输出value:MapReduceExtendedCell
- 将Mapper获取到Text文本行,转换为TransferRecord实体类
- 从实体类中获取ID,并转换为rowkey
- 使用KeyValue类构建单元格,每个需要写入到表中的字段都需要构建出来单元格
- 使用context.write将输出输出 a) 构建输出key:new ImmutableBytesWrite(rowkey) b) 构建输出的value:new MapReduceExtendedCell(keyvalue对象)
2、Driver
- 实现步骤:
- 使用HBaseConfiguration.create()加载配置文件
- 创建HBase连接
- 获取HTable
- 构建MapReduce JOB a)、使用Job.getInstance构建一个Job对象 b)、调用setJarByClass设置要执行JAR包的class c)、调用setInputFormatClass为TextInputFormat.class d)、设置MapperClass e)、设置输出键Output Key Class f)、设置输出值Output Value Class g)、设置输入输出到HDFS的路径,输入路径/bank/input,输出路径/bank/output
1 、FileInputFormat.setInputPaths 2、FileOutputFormat.setOutputPath
h)、使用connection.getRegionLocator获取HBase Region的分布情况 i)、使用HFileOutputFormat2.configureIncrementalLoad配置HFile输出 5. 调用job.waitForCompletion执行MapReduce程序
package org.hbase.mr.bank;
import java.io.IOException;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.conf.Configured;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.KeyValue;
import org.apache.hadoop.hbase.TableName;
import org.apache.hadoop.hbase.client.Connection;
import org.apache.hadoop.hbase.client.ConnectionFactory;
import org.apache.hadoop.hbase.client.RegionLocator;
import org.apache.hadoop.hbase.client.Table;
import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
import org.apache.hadoop.hbase.mapreduce.HFileOutputFormat2;
import org.apache.hadoop.hbase.util.Bytes;
import org.apache.hadoop.hbase.util.MapReduceExtendedCell;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.Mapper;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;
import org.apache.hadoop.util.Tool;
import org.apache.hadoop.util.ToolRunner;
import org.springframework.util.StopWatch;
public class BankRecordBulkLoad extends Configured implements Tool {
static String in = "hdfs://HadoopHAcluster/hbasetest/bank_bu/in";
static String out = "hdfs://HadoopHAcluster/hbasetest/bank_bu/out";
TableName tableName = TableName.valueOf("BANK_BU:TRANSFER_RECORD");
public static void main(String[] args) throws Exception {
StopWatch clock = new StopWatch();
clock.start(BankRecordBulkLoad.class.getSimpleName());
// 1. 使用HBaseConfiguration.create()加载配置文件
System.setProperty("HADOOP_USER_NAME", "alanchan");
Configuration configuration = HBaseConfiguration.create();
configuration.set("fs.defaultFS", "hdfs://HadoopHAcluster");
configuration.set("dfs.nameservices", "HadoopHAcluster");
configuration.set("dfs.ha.namenodes.HadoopHAcluster", "nn1,nn2");
configuration.set("dfs.namenode.rpc-address.HadoopHAcluster.nn1", "server1:8020");
configuration.set("dfs.namenode.rpc-address.HadoopHAcluster.nn2", "server2:8020");
configuration.set("dfs.client.failover.proxy.provider.HadoopHAcluster","org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider");
int status = ToolRunner.run(configuration, new BankRecordBulkLoad(), args);
System.exit(status);
clock.stop();
System.out.println(clock.prettyPrint());
}
@Override
public int run(String[] args) throws Exception {
// 2. 创建HBase连接
Connection connection = ConnectionFactory.createConnection(getConf());
// 3. 获取HTable
Table table = connection.getTable(tableName);
Job job = Job.getInstance(getConf(), this.getClass().getName());
job.setJarByClass(this.getClass());
job.setMapperClass(BankRecordBulkLoadMapper.class);
job.setMapOutputKeyClass(ImmutableBytesWritable.class);
job.setMapOutputValueClass(MapReduceExtendedCell.class);
FileInputFormat.setInputPaths(job, new Path(in));
Path outputDir = new Path(out);
outputDir.getFileSystem(this.getConf()).delete(outputDir, true);
FileOutputFormat.setOutputPath(job, outputDir);
// h) 使用connection.getRegionLocator获取HBase Region的分布情况
RegionLocator regionLocator = connection.getRegionLocator(tableName);
// i) 使用HFileOutputFormat2.configureIncrementalLoad配置HFile输出
HFileOutputFormat2.configureIncrementalLoad(job, table, regionLocator);
return job.waitForCompletion(true) ? 0 : 1;
}
/**
* 1. 创建一个BankRecordMapper的类继承Mapper类,
* Mapper的泛型为
* a) 输入key:LongWritable
* b)输入value:Text
* c) 输出key:ImmutableBytesWritable
* d) 输出value:MapReduceExtendedCell
* 2. 将Mapper获取到Text文本行,转换为TransferRecord实体类
* 3. 从实体类中获取ID,并转换为rowkey
* 4.使用KeyValue类构建单元格,每个需要写入到表中的字段都需要构建出来单元格
* 5. 使用context.write将输出输出
* a) 构建输出key:new ImmutableBytesWrite(rowkey)
* b) 构建输出的value:new MapReduceExtendedCell(keyvalue对象)
*/
static class BankRecordBulkLoadMapper extends Mapper<LongWritable, Text, ImmutableBytesWritable, MapReduceExtendedCell> {
protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {
// 将Mapper获取到Text文本行,转换为TransferRecord实体类
// 7e59c946-b1c6-4b04-a60a-f69c7a9ef0d6,SU8sXYiQgJi8,6225681772493291,杭州银行,丁杰,4896117668090896,
// 卑文彬,节日快乐,电脑客户端,电子银行转账,转账完成,2020-5-13 21:06:92,11659.0
TransferRecord transferRecord = parse(value.toString());
// 从实体类中获取ID,并转换为rowkey
String rowkeyString = transferRecord.getId();
byte[] rowkeyByteArray = Bytes.toBytes(rowkeyString);
byte[] columnFamily = Bytes.toBytes("C1");
byte[] colId = Bytes.toBytes("id");
byte[] colCode = Bytes.toBytes("code");
byte[] colRec_account = Bytes.toBytes("rec_account");
byte[] colRec_bank_name = Bytes.toBytes("rec_bank_name");
byte[] colRec_name = Bytes.toBytes("rec_name");
byte[] colPay_account = Bytes.toBytes("pay_account");
byte[] colPay_name = Bytes.toBytes("pay_name");
byte[] colPay_comments = Bytes.toBytes("pay_comments");
byte[] colPay_channel = Bytes.toBytes("pay_channel");
byte[] colPay_way = Bytes.toBytes("pay_way");
byte[] colStatus = Bytes.toBytes("status");
byte[] colTimestamp = Bytes.toBytes("timestamp");
byte[] colMoney = Bytes.toBytes("money");
// 构建输出key:new ImmutableBytesWrite(rowkey)
ImmutableBytesWritable immutableBytesWritable = new ImmutableBytesWritable(rowkeyByteArray);
// 使用KeyValue类构建单元格,每个需要写入到表中的字段都需要构建出来单元格
KeyValue kvId = new KeyValue(rowkeyByteArray, columnFamily, colId, Bytes.toBytes(transferRecord.getId()));
KeyValue kvCode = new KeyValue(rowkeyByteArray, columnFamily, colCode, Bytes.toBytes(transferRecord.getCode()));
KeyValue kvRec_account = new KeyValue(rowkeyByteArray, columnFamily, colRec_account, Bytes.toBytes(transferRecord.getRec_account()));
KeyValue kvRec_bank_name = new KeyValue(rowkeyByteArray, columnFamily, colRec_bank_name, Bytes.toBytes(transferRecord.getRec_bank_name()));
KeyValue kvRec_name = new KeyValue(rowkeyByteArray, columnFamily, colRec_name, Bytes.toBytes(transferRecord.getRec_name()));
KeyValue kvPay_account = new KeyValue(rowkeyByteArray, columnFamily, colPay_account, Bytes.toBytes(transferRecord.getPay_account()));
KeyValue kvPay_name = new KeyValue(rowkeyByteArray, columnFamily, colPay_name, Bytes.toBytes(transferRecord.getPay_name()));
KeyValue kvPay_comments = new KeyValue(rowkeyByteArray, columnFamily, colPay_comments, Bytes.toBytes(transferRecord.getPay_comments()));
KeyValue kvPay_channel = new KeyValue(rowkeyByteArray, columnFamily, colPay_channel, Bytes.toBytes(transferRecord.getPay_channel()));
KeyValue kvPay_way = new KeyValue(rowkeyByteArray, columnFamily, colPay_way, Bytes.toBytes(transferRecord.getPay_way()));
KeyValue kvStatus = new KeyValue(rowkeyByteArray, columnFamily, colStatus, Bytes.toBytes(transferRecord.getStatus()));
KeyValue kvTimestamp = new KeyValue(rowkeyByteArray, columnFamily, colTimestamp, Bytes.toBytes(transferRecord.getTimestamp()));
KeyValue kvMoney = new KeyValue(rowkeyByteArray, columnFamily, colMoney, Bytes.toBytes(transferRecord.getMoney()));
// 使用context.write将输出输出
// 构建输出的value:new MapReduceExtendedCell(keyvalue对象)
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvId));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvCode));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvRec_account));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvRec_bank_name));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvRec_name));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvPay_account));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvPay_name));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvPay_comments));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvPay_channel));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvPay_way));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvStatus));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvTimestamp));
context.write(immutableBytesWritable, new MapReduceExtendedCell(kvMoney));
}
}
// 通过调用parse方法就可以将一行逗号分隔的文本解析为TransferRecord
public static TransferRecord parse(String line) {
String[] fieldsArray = line.split(",");
TransferRecord transferRecord = new TransferRecord();
transferRecord.setId(fieldsArray[0]);
transferRecord.setCode(fieldsArray[1]);
transferRecord.setRec_account(fieldsArray[2]);
transferRecord.setRec_bank_name(fieldsArray[3]);
transferRecord.setRec_name(fieldsArray[4]);
transferRecord.setPay_account(fieldsArray[5]);
transferRecord.setPay_name(fieldsArray[6]);
transferRecord.setPay_comments(fieldsArray[7]);
transferRecord.setPay_channel(fieldsArray[8]);
transferRecord.setPay_way(fieldsArray[9]);
transferRecord.setStatus(fieldsArray[10]);
transferRecord.setTimestamp(fieldsArray[11]);
transferRecord.setMoney(fieldsArray[12]);
return transferRecord;
}
}
3、加载数据文件到HBase
[alanchan@server4 testMR]$ hbase org.apache.hadoop.hbase.tool.LoadIncrementalHFiles /hbasetest/bank_bu/out BANK_BU:TRANSFER_RECORD
4、验证
1)、hbase shell验证
hbase(main):016:0> count 'BANK_BU:TRANSFER_RECORD'
Current count: 1000, row: 008319c6-871d-4c65-a6e1-a96def6034ee
...
Current count: 500000, row: ffff98c5-0ca0-490a-85f4-acd4ef873362
500000 row(s)
Took 31.7712 seconds
=> 500000
hbase(main):020:0> get 'BANK_BU:TRANSFER_RECORD' ,'ffff98c5-0ca0-490a-85f4-acd4ef873362',{FORMATTER=>'toString'}
COLUMN CELL
C1:code timestamp=1665379929760, value=VM2VOZiRUFXK
C1:id timestamp=1665379929760, value=ffff98c5-0ca0-490a-85f4-acd4ef873362
C1:money timestamp=1665379929760, value=6941.0
C1:pay_account timestamp=1665379929760, value=1522996699509358
C1:pay_channel timestamp=1665379929760, value=电脑客户端
C1:pay_comments timestamp=1665379929760, value=昨晚的
C1:pay_name timestamp=1665379929760, value=武巧玲
C1:pay_way timestamp=1665379929760, value=电子银行转账
C1:rec_account timestamp=1665379929760, value=6225482875186251
C1:rec_bank_name timestamp=1665379929760, value=哈尔滨银行
C1:rec_name timestamp=1665379929760, value=施晓娜
C1:status timestamp=1665379929760, value=转账完成
C1:timestamp timestamp=1665379929760, value=2020-5-13 21:06:57
1 row(s)
Took 0.0070 seconds
1)、在phoenix客户端创建视图验证
注意:表字段的的引号和命名空间的引号,hbase中创建的表的字段是小写字母
CREATE view "BANK_BU"."TRANSFER_RECORD"
(
"id" varchar primary key,
"C1"."code" varchar,
"C1"."rec_account" varchar,
"C1"."rec_bank_name" varchar,
"C1"."rec_name" varchar,
"C1"."pay_account" varchar,
"C1"."pay_name" varchar,
"C1"."pay_comments" varchar,
"C1"."pay_channel" varchar,
"C1"."status" varchar,
"C1"."timestamp" varchar,
"C1"."money" varchar,
"C1"."pay_way" varchar
);
0: jdbc:phoenix:server2:2118> CREATE view "BANK_BU"."TRANSFER_RECORD"
. . . . . . . . . . . . . . > (
. . . . . . . . . . . . . . > "id" varchar primary key,
. . . . . . . . . . . . . . > "C1"."code" varchar,
. . . . . . . . . . . . . . > "C1"."rec_account" varchar,
. . . . . . . . . . . . . . > "C1"."rec_bank_name" varchar,
. . . . . . . . . . . . . . > "C1"."rec_name" varchar,
. . . . . . . . . . . . . . > "C1"."pay_account" varchar,
. . . . . . . . . . . . . . > "C1"."pay_name" varchar,
. . . . . . . . . . . . . . > "C1"."pay_comments" varchar,
. . . . . . . . . . . . . . > "C1"."pay_channel" varchar,
. . . . . . . . . . . . . . > "C1"."status" varchar,
. . . . . . . . . . . . . . > "C1"."timestamp" varchar,
. . . . . . . . . . . . . . > "C1"."money" varchar,
. . . . . . . . . . . . . . > "C1"."pay_way" varchar
. . . . . . . . . . . . . . > );
No rows affected (0.028 seconds)
0: jdbc:phoenix:server2:2118> !table
+------------+--------------+------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+---+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT | VIEW_TYPE | INDEX_TYPE | TRANSACTIONAL | IS_NAMESPACE_MAPPED | G |
+------------+--------------+------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+---+
| | SYSTEM | CATALOG | SYSTEM TABLE | | | | | | false | null | false | | | | false | true | n |
| | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | | false | null | false | | | | false | true | n |
| | SYSTEM | LOG | SYSTEM TABLE | | | | | | true | 32 | false | | | | false | true | n |
| | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | | false | null | false | | | | false | true | n |
| | SYSTEM | STATS | SYSTEM TABLE | | | | | | false | null | false | | | | false | true | n |
| | | WB | VIEW | | | | | | false | null | false | | MAPPED | | false | true | n |
| | BANK_BU | TRANSFER_RECORD | VIEW | | | | | | false | null | false | | MAPPED | | false | true | n |
+------------+--------------+------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+---+
0: jdbc:phoenix:server2:2118> select * from BANK_BU.TRANSFER_RECORD limit 10 offset 0;
+---------------------------------------+---------------+-------------------+----------------+-----------+-------------------+-----------+---------------+--------------+---------+----------------------+----------+----------+
| id | code | rec_account | rec_bank_name | rec_name | pay_account | pay_name | pay_comments | pay_channel | status | timestamp | money | pay_way |
+---------------------------------------+---------------+-------------------+----------------+-----------+-------------------+-----------+---------------+--------------+---------+----------------------+----------+----------+
| 000008c4-4ab2-4d22-b083-c8d15fed15f3 | IwuxL0emfwrK | 6225506305682240 | 中国建设银行 | 六振艳 | 4520750834609336 | 时妍 | 收到,请回复 | 手机银行 | 转账完成 | 2020-5-13 21:06:124 | 20557.0 | 电子银行转账 |
| 00001127-bf89-4f2b-af1a-eda51cd31685 | XqxRbaEvOZTP | 6225256780794589 | 北京银行 | 叔志刚 | 3956591711781354 | 乌红波 | 房贷 | 柜台 | 转账完成 | 2020-5-13 21:06:52 | 23690.0 | 电子银行转账 |
| 00003cba-5797-4958-a6b6-b1b29a82f135 | QkLU70g0Z0GO | 6225211014197441 | 中国民生银行 | 阙高峰 | 2038458857630095 | 礼振州 | 房贷 | 电脑客户端 | 转账完成 | 2020-5-13 21:06:121 | 7129.0 | 电子银行转账 |
| 00005a13-42f6-4c01-ba0d-1309dd0f7b63 | LIXOTl3xFRBn | 6225957713694544 | 西安银行 | 示佳杰 | 1971641953509295 | 时妍 | 收到,请回复 | 柜台 | 转账完成 | 2020-5-13 21:06:96 | 6941.0 | 电子银行转账 |
| 00005a74-54ac-45f6-bb7c-4aebe681b2bf | vpcSyQufGqiV | 6225878690779441 | 杭州银行 | 丁杰 | 8736879534356975 | 季丹丹 | 昨晚的 | 手机银行 | 转账完成 | 2020-5-13 21:06:118 | 20557.0 | 电子银行转账 |
| 00009167-7f36-4bae-8a9f-d27f16589892 | 4180cLRolIu1 | 6225158534379909 | 中国民生银行 | 节文勇 | 2591153963048481 | 钊宝娥 | 房贷 | 手机银行 | 转账完成 | 2020-5-13 21:06:88 | 10057.0 | 电子银行转账 |
| 0000996e-13d7-4c32-8e10-1e0a1ee66d57 | euqcO6IamYoq | 6225594612590037 | 中国光大银行 | 潭晶 | 5862522867517733 | 赧天春 | 房贷 | 手机银行 | 转账完成 | 2020-5-13 21:06:60 | 15067.0 | 电子银行转账 |
| 0000ae4f-38da-456a-ab8f-bf4696b2eb5e | okTbdzNgktN5 | 6225717251023277 | 中国中信银行 | 陆福跃 | 7979323755051525 | 闳静 | 娜娜,想你了 | 手机银行 | 转账完成 | 2020-5-13 21:06:82 | 20295.0 | 电子银行转账 |
| 000107f3-fe97-4f85-a41c-7d888a541d58 | NbjDfkujrRZM | 6225645778610138 | 深圳发展银行 | 阙高峰 | 3746515314112373 | 廉兴瑞 | 房贷 | 手机银行 | 转账完成 | 2020-5-13 21:06:48 | 18689.0 | 电子银行转账 |
| 00012e8c-d364-414e-9c18-083fff5a13ee | meLawRw66Dyi | 6225376344744584 | 中国银行 | 留兰兰 | 6233127464571785 | 皮红辰 | 房贷 | 手机银行 | 转账完成 | 2020-5-13 21:06:85 | 11310.0 | 电子银行转账 |
+---------------------------------------+---------------+-------------------+----------------+-----------+-------------------+-----------+---------------+--------------+---------+----------------------+----------+----------+
10 rows selected (0.058 seconds)