spark-hbase-BulkLoad
hbase 2.X 的版本,适用
背景:hive表中的数据导入hbase表使用,大概分两种方式操作。各有利弊
方法1:先建 hbase表,再建hbase的外表hive表,做好字段映射,起一个MR 任务写入 hive外表,
优点:简单,
缺点: 数据量较大会建成hbase regionserver 压力大,可能会导致regionserver not online ,宕机,造成服务不可用的状态,数据量小没事
hbase 建表
create 'dim_goods_xxxxx',{NAME=>'cf',VERSIONS=>'1',TTL => '604800',COMPRESSION =>'SNAPPY',CONFIGURATION=>{'hbase.hstore.blockingStoreFiles'=>'8'}},{NUMREGIONS => 30, SPLITALGO => 'HexStringSplit'}
hive 建 hbase 外表
CREATE EXTERNAL TABLE `xxx.dim_goods_xxxxx`(
`key` string COMMENT '',
`data_from` string COMMENT '企业id',
`goods_id` string COMMENT '商品id')
ROW FORMAT SERDE
'org.apache.hadoop.hive.hbase.HBaseSerDe'
STORED BY
'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
'hbase.columns.mapping'=':key,cf:data_from,cf:goods_id',
'serialization.format'='1')
TBLPROPERTIES (
'hbase.table.name'='dim_goods_xxxxx',
'last_modified_by'='hive',
'last_modified_time'='1722103676',
'transient_lastDdlTime'='1722103676')
;
注意 hbase.columns.mapping 中的字段 映射
JOB
insert into xxx.dim_goods_xxxxx select key,data_from,goods_id from xxx.xxxx;
方法2: BulkLoad 的方式导入,spark 读取 hive 表,写入hbase 需要的 HFile 类型的 文件,写在 hdfs 上面,再使用BulkLoad ,把数据加载移动到hbase表中
优点:hbase regionserver 无感知无压力
缺点: 开发写代码
步骤
1:hbase 中建表,做好预分区,合理分区,太大,太小都不好。
2:spark session 读取 rdd 数据做 df
3: 把df 数据 包装成 hbase 需要的 HFile ( JavaPairRDD<ImmutableBytesWritable, KeyValue> hbaseDF2), 格式写入hdfs (saveAsNewAPIHadoopFile)
3中有一些注意的细节,不处理好,写入不会成功的
3.1: hFile 写入一定要排序,不按这个排序,数据量小可以成功,数据量大肯定不会成功。这个很重要, rowkey + family + column 一起排序才可以 ,不按这个顺序排
3.2:根据hbase的预分区做分区内和排序 repartitionAndSortWithinPartitions
3.3写入saveAsNewAPIHadoopFile
4.LoadIncrementalHFiles doBulkLoad 完成
具体参见代码
public class SparkHBaseSumit {
private static SparkSession spark = null;
public static void main(String[] args) throws IOException, InterruptedException, ClassNotFoundException {
HashMap<String, String> argMap = new HashMap<String, String>();
if (args.length > 0) {
for (String arg : args) {
int firstDelimiterIndex = arg.trim().indexOf("=");
if (firstDelimiterIndex != -1) {
String firstPart = arg.substring(0, firstDelimiterIndex);
String restPart = arg.substring(firstDelimiterIndex + 1);
argMap.put(firstPart, restPart);
}
}
} else {
return;
}
Long begin = System.currentTimeMillis();
String sparkSql = (argMap.containsKey("sparkSql")) ? argMap.get("sparkSql") : "";
String hbaseTable = (argMap.containsKey("hbaseTable")) ? argMap.get("hbaseTable") : "";
String cf = (argMap.containsKey("family")) ? argMap.get("family") : "cf";
String p = (argMap.containsKey("p")) ? argMap.get("p") : "";
String appName = (argMap.containsKey("appName")) ? argMap.get("appName") : "spark_hbase_bluckload_" + hbaseTable;
if ("".equals(sparkSql) || "".equals(hbaseTable)) {
System.out.println("sparkSql hbaseTable is not null");
return;
}
spark = getSparkSession(appName);
if(!"".equals(p)){
spark.conf().set("spark.sql.shuffle.partitions", p);
}
Dataset<Row> df = spark.sql(sparkSql);
df.show();
String[] columns = df.columns();
String rowkey = columns[0];
List<String> columnsList = new ArrayList<>(Arrays.asList(columns));
columnsList.remove(rowkey);
String[] cols = columnsList.toArray(new String[columnsList.size()]);
Arrays.sort(cols);
byte[] family = Bytes.toBytes(cf);
byte[][] colBytes = new byte[cols.length][];
for (int i = 0; i < cols.length; i++) {
colBytes[i] = Bytes.toBytes(cols[i]);
}
JavaPairRDD<Tuple2<ImmutableBytesWritable, byte[]>, KeyValue> hbaseDF = df.javaRDD().flatMapToPair(row -> HBaseTableUtil.generateHBaseRowWithQualifier(row, rowkey, cf, family, cols, colBytes).iterator());
Long cost = (System.currentTimeMillis() - begin) / 60000;
System.out.println("rdd cost time = " + cost + " m");
FileSystem fileSystem = FileSystem.get(spark.sparkContext().hadoopConfiguration());
// 如果存放 HFile文件的路径已经存在,就删除掉
String hFilePath = "/tmp/" + appName;
if (fileSystem.exists(new Path(hFilePath))) {
fileSystem.delete(new Path(hFilePath), true);
System.out.println("del hdfs dir" + hFilePath);
}
Configuration conf = HBaseConfiguration.create();
conf.set("hbase.zookeeper.quorum", "xxxxxxxxxxx");
conf.set("hbase.zookeeper.znode.parent", "/hbase");
conf.set("hbase.zookeeper.property.clientPort", "2181");
conf.set("hbase.client.ipc.pool.size", "10");
conf.set("hbase.mapreduce.hfileoutputformat.table.name", hbaseTable);
conf.set("hbase.mapreduce.hfileoutputformat.compression", "gz");
// conf.set("hbase.mapreduce.hfileoutputformat.compression", "snappy");
// conf.set("hbase.mapreduce.hfileoutputformat.compress", "true");
// conf.set("hbase.mapreduce.hfileoutputformat.compress.type", "BLOCK");
// conf.set("hbase.mapreduce.hfileoutputformat.compress.codec", "org.apache.hadoop.io.compress.SnappyCodec");
conf.set("hhbase.bulkload.retries.number", "0");
// 将HFile文件加载到HBase中,先创建一个类
// 创建Job实例并设置输出格式
Job job = Job.getInstance(conf);
job.setJobName(hbaseTable);
job.setMapOutputKeyClass(ImmutableBytesWritable.class);
job.setMapOutputValueClass(KeyValue.class);
Connection connection = ConnectionFactory.createConnection(conf);
Admin admin = connection.getAdmin();
Table table = connection.getTable(TableName.valueOf(hbaseTable));
RegionLocator regionLocator = connection.getRegionLocator(TableName.valueOf(hbaseTable));
List<HRegionInfo> hRegionInfos = admin.getTableRegions(TableName.valueOf(hbaseTable));
ArrayList<String> regionSplits = new ArrayList<>();
for (HRegionInfo item : hRegionInfos) {
regionSplits.add(new String(item.getEndKey()));
}
regionSplits.remove(regionSplits.size() - 1);
JavaPairRDD<ImmutableBytesWritable, KeyValue> hbaseDF2 = null;
if (regionSplits.size() > 0) {
hbaseDF2 = hbaseDF.repartitionAndSortWithinPartitions(new RegionPartitioner(regionSplits.toArray(new String[regionSplits.size()])), new KeyQualifierComparator())
.mapToPair(row -> new Tuple2<>(row._1()._1(), row._2()));
} else {
hbaseDF2 = hbaseDF.mapToPair(row -> new Tuple2<>(row._1()._1(), row._2()));
}
hbaseDF2.saveAsNewAPIHadoopFile(hFilePath,
ImmutableBytesWritable.class,
KeyValue.class,
HFileOutputFormat2.class,
job.getConfiguration()
);
Long cost2 = (System.currentTimeMillis() - begin) / 60000;
System.out.println("write cost time = " + (cost2 - cost) + " m");
System.out.println("data write ok");
HFileOutputFormat2.configureIncrementalLoad(job, table, regionLocator);
LoadIncrementalHFiles load = new LoadIncrementalHFiles(conf);
//8.2用这个类返回值调用doBulkLoad开始加载;传入四个参数(输出路径,,mater对象admin,表名,region的信息)
load.doBulkLoad(new Path(hFilePath), admin, table, regionLocator);
Long cost3 = (System.currentTimeMillis() - begin) / 60000;
System.out.println("load cost time = " + (cost3 - cost2) + " m");
System.out.println("file load ok");
//清空目录
if (fileSystem.exists(new Path(hFilePath))) {
fileSystem.delete(new Path(hFilePath), true);
System.out.println("del hdfs dir" + hFilePath);
}
fileSystem.close();
admin.close();
connection.close();
spark.stop();
}
/**
* 生产spark hadoop conf
*
* @return
*/
private static SparkSession getSparkSession(String appName) {
// System.setProperty("HADOOP_USER_NAME", "hive");
SparkSession spark = SparkSession.builder()
// .master("local[*]")
.appName(appName)
.config(SQLConf.PARTITION_OVERWRITE_MODE().key(), "dynamic")
.config("spark.hadoop." + METASTOREURIS.varname, "thrift://xxxx")
.config("spark.sql.warehouse.dir", "/user/hive/warehouse")
.config("spark.executor.heartbeatInterval", "100000")
.config("spark.network.timeoutInterval", "100000")
.config("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
.config("spark.executor.extraJavaOptions", "-XX:+UseG1GC -XX:G1HeapRegionSize=32M -XX:+UseLargePages")
.enableHiveSupport()
.getOrCreate();
"org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider");
return spark;
}
}
rdd 处理代码
public class HBaseTableUtil {
public static List<Tuple2<Tuple2<ImmutableBytesWritable, byte[]>, KeyValue>> generateHBaseRowWithQualifier(Row row, String rowkey, String cf, byte[] family, String[] cols, byte[][] colBytes) {
List<Tuple2<Tuple2<ImmutableBytesWritable, byte[]>, KeyValue>> list = new LinkedList<>();
String rowkeyVal = row.getAs(rowkey).toString();
byte[] rowkeyByte = Bytes.toBytes(rowkeyVal);
ImmutableBytesWritable writable = new ImmutableBytesWritable(rowkeyByte);
Set<KeyValue> map = new TreeSet<KeyValue>(KeyValue.COMPARATOR);
int j = 0;
for (String col : cols) {
String val = (row.getAs(col) != null) ? row.getAs(col).toString() : "";
// KeyValue keyValue = new KeyValue(rowkeyByte, family, Bytes.toBytes(col), Bytes.toBytes(val));
KeyValue keyValue = new KeyValue(rowkeyByte, family, colBytes[j], Bytes.toBytes(val));
map.add(keyValue);
j++;
}
// 这个很重要, rowkey + family + column 一起排序才可以
int i = 0;
for (KeyValue kv : map) {
String familyAndQualifier = cf + ":" + cols[i];
list.add(new Tuple2<>(new Tuple2<>(writable, Bytes.toBytes(familyAndQualifier)), kv));
i++;
}
return list;
}
}
分区器代码
public class RegionPartitioner extends Partitioner {
private final String[] endKeys;
private final int numPartitions;
public RegionPartitioner(String[] endKeys){
this.endKeys = endKeys;
this.numPartitions = endKeys.length + 1;
}
@Override
public int numPartitions() {
return this.numPartitions;
}
@Override
public int getPartition(Object key) {
if (this.endKeys.length == 0) {
// 如果这个hbase表没有分区信息,则所有数据都写到一个文件里面
// 经测试,当前情况下,这个partition里面的数据不会进行排序,所以调用RegionPartitioner的时候就避免走到这一步
return 0;
}else if(key instanceof Tuple2 ){
if (((Tuple2) key)._1() instanceof ImmutableBytesWritable) {
byte[] keyBytes = ((ImmutableBytesWritable) ((Tuple2) key)._1()).copyBytes();
String comparedKey = new String(keyBytes).substring(0, endKeys[0].length());
for (int i = 0; i < this.endKeys.length; i++) {
if (comparedKey.compareTo(endKeys[i]) < 0) {
return i;
}
}
return endKeys.length;
}
} else if(key instanceof ImmutableBytesWritable) {
byte[] keyBytes = ((ImmutableBytesWritable) key).copyBytes();
String comparedKey = new String(keyBytes).substring(0, endKeys[0].length());
for (int i = 0; i < this.endKeys.length; i++) {
if (comparedKey.compareTo(endKeys[i]) < 0) {
return i;
}
}
return endKeys.length;
}
return 0;
}
}
排序 代码
public class KeyQualifierComparator implements Comparator<Tuple2<ImmutableBytesWritable, byte[]>>, Serializable {
@Override
public int compare(Tuple2<ImmutableBytesWritable, byte[]> o1, Tuple2<ImmutableBytesWritable, byte[]> o2) {
if (o1._1().compareTo(o2._1()) == 0) {
return Bytes.compareTo(o1._2(), o2._2());
} else {
return o1._1().compareTo(o2._1());
}
}
}
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.gaojihealth.bdp</groupId>
<artifactId>diff-data</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<spark.version>3.2.0</spark.version>
<scala.version>2.12</scala.version>
<flink.version>1.17.0</flink.version>
<commons.lang3.version>3.7</commons.lang3.version>
<commons.io.version>2.6</commons.io.version>
</properties>
<dependencies>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-core_${scala.version}</artifactId>
<version>${spark.version}</version>
<scope>provided</scope>
<exclusions>
<exclusion>
<groupId>org.xerial.snappy</groupId>
<artifactId>snappy-java</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_${scala.version}</artifactId>
<version>${spark.version}</version>
<scope>provided</scope>
<exclusions>
<exclusion>
<groupId>org.xerial.snappy</groupId>
<artifactId>snappy-java</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-hive_${scala.version}</artifactId>
<version>${spark.version}</version>
<scope>provided</scope>
<exclusions>
<exclusion>
<groupId>org.xerial.snappy</groupId>
<artifactId>snappy-java</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- hbase dependencies start -->
<dependency>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-client</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-common</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-server</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-mapreduce</artifactId>
<version>2.2.0</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-assembly-plugin</artifactId>
<version>3.0.0</version>
<configuration>
<archive>
</archive>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
</configuration>
<executions>
<execution>
<id>make-assembly</id> <!-- this is used for inheritance merges -->
<phase>package</phase> <!-- 指定在打包节点执行jar包合并操作 -->
<goals>
<goal>single</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
spark sumit 提交命令
#!/bin/bash
dt=`date -d "1 days ago" +%Y%m%d`
appName="spark_hbase_bluckload_dim_goods_xxxx"
sparkSql="select rowkey ,data_from ,goods_id ,is_otc ,atc1_new ,stjb ,main_goods_id ,flag_disease ,is_del ,dtp_atc ,is_gold ,class_code1 ,own_brand_flag from tmp.b;"
hbaseTable="dim:dim_goods_category_manual_all2"
family=cf
echo "$sparkSql"
export SPARK_HOME=/data/appdata/spark-3.2.0-bin-hadoop3.2
spark-submit \
--conf spark.yarn.submit.waitAppCompletion=true \
--conf spark.network.maxRemoteBlockSizeFetchToMem=2147483000 \
--conf spark.network.timeout=300000 \
--conf spark.rpc.message.maxSize=1024 \
--conf spark.executor.heartbeatInterval=300000 \
--conf spark.driver.maxResultSize=8G \
--master yarn \
--deploy-mode cluster \
--class task.SparkHBaseSumit \
--driver-memory 8G \
--executor-memory 32G \
--executor-cores 1 \
--num-executors 1 \
--name ${appName} \
--queue root.ODS \
hdfs://nameservice2/BDP/spark/spark-hbase-bluckload.jar appName="${appName}" sparkSql="${sparkSql}" hbaseTable="${hbaseTable}" family="${family}"
spark-hbase-bluckload.jar 为java 工程打好的包