HBase學習之二: hbase分頁查詢

anickname發表於2018-05-22

在hbase中可以使用scan做一些簡單的查詢，但是要實現多條件複雜查詢還需要藉助filter(過濾器)來完成，甚至還可以自定義filter實現個性化的需求，專案中需要分頁查詢，記錄了其中的核心程式碼，以便於查閱。

zookeeper.properties配置檔案內容：

hbase_zookeeper_quorum=xxx.com,xxx.com,xxx.com
zookeeper_znode_parent=/hbase

zookeeper叢集配置，一般hbase會交給zookeeper管理。

ConfigUtil為讀取配置檔案類：

package cp.app.batch.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

/**
 *
 * @author author
 *
 */

public class ConfigUtil {

	private Properties props = new Properties();

	public ConfigUtil(String file) {
	String path = ConfigUtil.class.getClassLoader()
	.getResource(file).getPath();
	InputStream is = null;
	try {
	is = new FileInputStream(path);
	props.load(is);
	} catch (IOException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	} finally {
	try {
	is.close();
	} catch (IOException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}
	}
	}

	public int getInt(String key) {
	return Integer.parseInt(props.getProperty(key));
	}

	public String getString(String key) {
	return props.getProperty(key);
	}

}

HBase 查詢工具類:

package cp.app.service.impl;

import java.io.IOException;
import java.io.InterruptedIOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.Cell;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.HColumnDescriptor;
import org.apache.hadoop.hbase.HTableDescriptor;
import org.apache.hadoop.hbase.MasterNotRunningException;
import org.apache.hadoop.hbase.TableName;
import org.apache.hadoop.hbase.TableNotFoundException;
import org.apache.hadoop.hbase.ZooKeeperConnectionException;
import org.apache.hadoop.hbase.client.Get;
import org.apache.hadoop.hbase.client.HBaseAdmin;
import org.apache.hadoop.hbase.client.HConnection;
import org.apache.hadoop.hbase.client.HConnectionManager;
import org.apache.hadoop.hbase.client.HTable;
import org.apache.hadoop.hbase.client.HTableInterface;
import org.apache.hadoop.hbase.client.Put;
import org.apache.hadoop.hbase.client.Result;
import org.apache.hadoop.hbase.client.ResultScanner;
import org.apache.hadoop.hbase.client.RetriesExhaustedWithDetailsException;
import org.apache.hadoop.hbase.client.Scan;
import org.apache.hadoop.hbase.client.coprocessor.AggregationClient;
import org.apache.hadoop.hbase.client.coprocessor.LongColumnInterpreter;
import org.apache.hadoop.hbase.filter.BinaryComparator;
import org.apache.hadoop.hbase.filter.CompareFilter;
import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp;
import org.apache.hadoop.hbase.filter.Filter;
import org.apache.hadoop.hbase.filter.FilterList;
import org.apache.hadoop.hbase.filter.PageFilter;
import org.apache.hadoop.hbase.filter.RowFilter;
import org.apache.hadoop.hbase.filter.SingleColumnValueFilter;
import org.apache.hadoop.hbase.filter.SubstringComparator;
import org.apache.hadoop.hbase.util.Bytes;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import cp.app.batch.utils.ConfigUtil;
import cp.app.comm.CpConstants;
import cp.app.service.HBaseService;

/**
 * HBase查詢與插入操作工具類
 * 
 * @author author
 *
 */
 //採用注入方式，HBaseService為定義的查詢介面，可不需要。 
@Service
public class HBaseServiceImpl implements HBaseService{

	private static Logger log = Logger.getLogger(HBaseServiceImpl.class.getName());

	static ConfigUtil util = new ConfigUtil("conf/zookeeper.properties");
	private static final String HBASE_ZOOKEEPER_QUORUM = util
			.getString("hbase_zookeeper_quorum");
	private static final String ZOOKEEPER_ZNODE_PARENT = util
			.getString("zookeeper_znode_parent");
	private static Configuration conf = HBaseConfiguration.create();
	static {
		conf.set("hbase.zookeeper.quorum", HBASE_ZOOKEEPER_QUORUM);
		conf.set("zookeeper.znode.parent", ZOOKEEPER_ZNODE_PARENT);
	}

	/**
	 * 建立表
	 * 
	 * @param tableName
	 *            表名
	 * @param columnFamily
	 *            列簇集合
	 * @return 成功-true 失敗-false
	 */
	@SuppressWarnings("resource")
	public boolean createTable(String tableName, List<String> columnFamily) {
		try {
			if (StringUtils.isBlank(tableName) || columnFamily == null
					|| columnFamily.size() < 0) {
				log.error("===Parameters tableName|columnFamily should not be null,Please check!===");
			}
			HBaseAdmin admin = new HBaseAdmin(conf);
			if (admin.tableExists(tableName)) {
				return true;
			} else {
				HTableDescriptor tableDescriptor = new HTableDescriptor(
						TableName.valueOf(tableName));
				for (String cf : columnFamily) {
					tableDescriptor.addFamily(new HColumnDescriptor(cf));
				}
				admin.createTable(tableDescriptor);
				log.info("===Create Table " + tableName
						+ " Success!columnFamily:" + columnFamily.toString()
						+ "===");
			}
		} catch (MasterNotRunningException e) {
			// TODO Auto-generated catch block
			log.error(e);
			return false;
		} catch (ZooKeeperConnectionException e) {
			// TODO Auto-generated catch block
			log.error(e);
			return false;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			log.error(e);
			return false;
		}
		return true;
	}

	/**
	 * 查詢單條記錄
	 * 
	 * @param tableName
	 *            表名
	 * @param rowKey
	 *            rowKey值
	 * @return 返回單條記錄
	 */
	public List<Map<String, String>> selectOneByRowKey(String tableName,
			String rowKey) {
		if (StringUtils.isBlank(rowKey) || StringUtils.isBlank(tableName)) {
			log.error("===Parameters tableName|rowKey should not be blank,Please check!===");
			return null;
		}
		List<Map<String, String>> rowList = new ArrayList<Map<String, String>>();
		try {
			Get get = new Get(Bytes.toBytes(rowKey));
			HTableInterface hTable = getHTable(tableName);
			if (hTable != null) {
				Result result = hTable.get(get);
				Map<String, String> cellMap = getRowByResult(result);
				rowList.add(cellMap);
			}
			hTable.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			log.error(e);
		}
		return rowList;
	}

	/**
	 * 分頁查詢表資料
	 * 
	 * @param tableName
	 *            表名
	 * @param ddate
	 *            資料日期
	 * @param pageSize
	 *            頁大小
	 * @param lastrowKey
	 *            起始rowkey值
	 * @return 返回查詢資料結果集
	 */
	public List<Map<String, String>> selectAllByPage(String tableName,
			String ddate, int pageSize, String lastrowKey) {
		if (StringUtils.isBlank(tableName) || StringUtils.isBlank(ddate)
				|| StringUtils.isBlank(pageSize + "")
				|| StringUtils.isBlank(lastrowKey)) {
			log.error("===Parameters tableName|ddate|pageSize|rowKey should not be blank,Please check!===");
			return null;
		}
		HTable hTable = (HTable) getHTable(tableName);
		Scan scan = new Scan();
		FilterList filterList = new FilterList(
				FilterList.Operator.MUST_PASS_ALL);
		Filter rowFilter1 = new RowFilter(CompareFilter.CompareOp.EQUAL,
				new SubstringComparator(ddate));
		Filter pageFilter = new PageFilter(pageSize);
		filterList.addFilter(rowFilter1);
		filterList.addFilter(pageFilter);
		if (!CpConstants.ROWKEY_FIRST.equals(lastrowKey)) {
			Filter rowFilter2 = new RowFilter(CompareFilter.CompareOp.GREATER,
					new BinaryComparator(Bytes.toBytes(lastrowKey)));
			filterList.addFilter(rowFilter2);
		}
		scan.setFilter(filterList);
		List<Map<String, String>> lists = new ArrayList<Map<String, String>>();
		try {
			ResultScanner rs = hTable.getScanner(scan);
			for (Result result : rs) {
				lists.add(getRowByResult(result));
			}
			hTable.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			log.error(e);
		}
		return lists;
	}

	/**
	 * 根據狀態分頁查詢表資料
	 * 
	 * @param tableName
	 *            表名
	 * @param ddate
	 *            資料日期
	 * @param pageSize
	 *            頁大小
	 * @param lastrowKey
	 *            起始rowkey值
	 * @param status
	 *            傳送狀態
	 * @return 返回查詢資料結果集
	 */
	public List<Map<String, String>> selectAllByPageStatus(String tableName,
			String ddate, int pageSize, String lastrowKey, String status) {
		if (StringUtils.isBlank(tableName) || StringUtils.isBlank(ddate)
				|| StringUtils.isBlank(pageSize + "")
				|| StringUtils.isBlank(lastrowKey)) {
			log.error("===Parameters tableName|ddate|pageSize|rowKey should not be blank,Please check!===");
			return null;
		}
		HTable hTable = (HTable) getHTable(tableName);
		Scan scan = new Scan();
		FilterList filterList = new FilterList(
				FilterList.Operator.MUST_PASS_ALL);
		filterList
				.addFilter(new SingleColumnValueFilter(Bytes.toBytes("info"),
						Bytes.toBytes("status"), CompareOp.EQUAL, Bytes
								.toBytes(status)));
		Filter rowFilter1 = new RowFilter(CompareFilter.CompareOp.EQUAL,
				new SubstringComparator(ddate));
		Filter pageFilter = new PageFilter(pageSize);
		filterList.addFilter(rowFilter1);
		filterList.addFilter(pageFilter);
		if (!CpConstants.ROWKEY_FIRST.equals(lastrowKey)) {
			Filter rowFilter2 = new RowFilter(CompareFilter.CompareOp.GREATER,
					new BinaryComparator(Bytes.toBytes(lastrowKey)));
			filterList.addFilter(rowFilter2);
		}
		scan.setFilter(filterList);
		List<Map<String, String>> lists = new ArrayList<Map<String, String>>();
		try {
			ResultScanner rs = hTable.getScanner(scan);
			for (Result result : rs) {
				lists.add(getRowByResult(result));
			}
			hTable.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			log.error(e);
		}
		return lists;
	}

	/**
	 * 獲取頁數
	 * 
	 * @param tableName
	 *            表名
	 * @param ddate
	 *            資料日期
	 * @param pageSize
	 *            分頁大小
	 * @return 返回頁數
	 */
	public int getPages(String tableName, String ddate, int pageSize) {
		if (StringUtils.isBlank(tableName) || StringUtils.isBlank(ddate)
				|| StringUtils.isBlank(pageSize + "")) {
			log.error("===Parameters tableName|ddate|pageSize should not be blank,Please check!===");
			return 0;
		}
		enableAggregation(tableName);
		int total = 0;
		try {
			HTable hTable = (HTable) getHTable(tableName);
			Scan scan = new Scan();
			Filter rowFilter = new RowFilter(CompareFilter.CompareOp.EQUAL,
					new SubstringComparator(ddate));
			scan.setFilter(rowFilter);
			AggregationClient aggregation = new AggregationClient(conf);
			Long count = aggregation.rowCount(hTable,
					new LongColumnInterpreter(), scan);
			total = count.intValue();
			hTable.close();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			log.error(e);
		}
		return (total % pageSize == 0) ? total / pageSize
				: (total / pageSize) + 1;
	}

	/**
	 * 根據傳送狀態獲取頁數
	 * 
	 * @param tableName
	 *            表名
	 * @param ddate
	 *            資料日期
	 * @param pageSize
	 *            分頁大小
	 * @param status
	 *            傳送狀態
	 * @return 返回頁數
	 */
	public int getPagesByStatus(String tableName, String ddate, int pageSize,
			String status) {
		if (StringUtils.isBlank(tableName) || StringUtils.isBlank(ddate)
				|| StringUtils.isBlank(pageSize + "")
				|| StringUtils.isBlank(status)) {
			log.error("===Parameters tableName|ddate|pageSize|status should not be blank,Please check!===");
			return 0;
		}
		enableAggregation(tableName);
		int total = 0;
		try {
			HTable hTable = (HTable) getHTable(tableName);
			Scan scan = new Scan();
			FilterList filterList = new FilterList(
					FilterList.Operator.MUST_PASS_ALL);
			Filter rowFilter = new RowFilter(CompareFilter.CompareOp.EQUAL,
					new SubstringComparator(ddate));
			filterList.addFilter(rowFilter);
			filterList.addFilter(new SingleColumnValueFilter(Bytes
					.toBytes("info"), Bytes.toBytes("status"), CompareOp.EQUAL,
					Bytes.toBytes(status)));
			scan.setFilter(filterList);
			AggregationClient aggregation = new AggregationClient(conf);
			Long count = aggregation.rowCount(hTable,
					new LongColumnInterpreter(), scan);
			total = count.intValue();
			hTable.close();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			log.error(e);
		}
		return (total % pageSize == 0) ? total / pageSize
				: (total / pageSize) + 1;
	}

	/**
	 * 獲取同一個rowkey下的記錄集合
	 * 
	 * @param result
	 *            結果集
	 * @return
	 */
	private Map<String, String> getRowByResult(Result result) {
		if (result == null) {
			log.error("===Parameter |result| should not be null,Please check!===");
			return null;
		}
		Map<String, String> cellMap = new HashMap<String, String>();
		for (Cell cell : result.listCells()) {
			String rowkey = Bytes.toString(cell.getRowArray(),
					cell.getRowOffset(), cell.getRowLength());
			String cf = Bytes.toString(cell.getFamilyArray(),
					cell.getFamilyOffset(), cell.getFamilyLength());
			String qf = Bytes.toString(cell.getQualifierArray(),
					cell.getQualifierOffset(), cell.getQualifierLength());
			String value = Bytes.toString(cell.getValueArray(),
					cell.getValueOffset(), cell.getValueLength());
			cellMap.put(CpConstants.HBASE_TABLE_PROP_ROWKEY, rowkey);
			cellMap.put(CpConstants.HBASE_TABLE_PROP_COLUMNFAMILY, cf);
			cellMap.put(qf, value);
		}
		return cellMap;
	}

	/**
	 * 獲取HTableInterface
	 * 
	 * @param tableName
	 *            表名
	 * @return 返回HTableInterface例項
	 */
	private HTableInterface getHTable(String tableName) {
		if (StringUtils.isBlank(tableName)) {
			log.error("===Parameter |tableName| should not be blank,Please check!===");
			return null;
		}
		HTableInterface hTable = null;
		try {
			HConnection conn = HConnectionManager.createConnection(conf);
			hTable = conn.getTable(Bytes.toBytes(tableName));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			log.error(e);
			return null;
		}
		return hTable;
	}

	/**
	 * 批量插入或更新
	 * 
	 * @param tableName
	 *            表名
	 * @param paraList
	 *            組裝成json或xml後的引數
	 * @return 成功-true 失敗-false
	 */
	public boolean batchPut(String tableName, List<Map<String, String>> paraList) {
		try {
			List<Put> puts = new ArrayList<Put>();
			for (Map<String, String> map : paraList) {
				Put put = getPutByMap(map);
				puts.add(put);
			}
			HTable hTable = (HTable) getHTable(tableName);
			hTable.put(puts);
			hTable.close();
		} catch (RetriesExhaustedWithDetailsException e) {
			// TODO Auto-generated catch block
			log.error(e);
			return false;
		} catch (InterruptedIOException e) {
			// TODO Auto-generated catch block
			log.error(e);
			return false;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			log.error(e);
			return false;
		}
		return true;
	}

	/**
	 * 根據map返回put
	 * 
	 * @param paraMap
	 *            引數map
	 * @return 返回put
	 */
	private Put getPutByMap(Map<String, String> paraMap) {
		if (paraMap == null) {
			log.error("===Parameter |paraMap| should not be null,Please check!===");
			return null;
		}
		Set<Entry<String, String>> set = paraMap.entrySet();
		Iterator<Entry<String, String>> it = set.iterator();
		byte[] rowkey = Bytes.toBytes(paraMap
				.get(CpConstants.HBASE_TABLE_PROP_ROWKEY));
		byte[] columnfamily = Bytes.toBytes(paraMap
				.get(CpConstants.HBASE_TABLE_PROP_COLUMNFAMILY));
		Put put = new Put(rowkey);
		while (it.hasNext()) {
			Entry<String, String> entry = it.next();
			String key = entry.getKey();
			if (!CpConstants.HBASE_TABLE_PROP_ROWKEY.equals(key)
					&& !CpConstants.HBASE_TABLE_PROP_COLUMNFAMILY.equals(key)) {
				String value = entry.getValue();
				put.add(columnfamily, Bytes.toBytes(key), Bytes.toBytes(value));
			}
		}
		return put;
	}

	/**
	 * 使表具有聚合功能
	 * 
	 * @param tableName
	 *            表名
	 */
	@SuppressWarnings("resource")
	private void enableAggregation(String tableName) {
		String coprocessorName = "org.apache.hadoop.hbase.coprocessor.AggregateImplementation";
		try {
			HBaseAdmin admin = new HBaseAdmin(conf);
			HTableDescriptor htd = admin.getTableDescriptor(Bytes
					.toBytes(tableName));
			List<String> coprocessors = htd.getCoprocessors();
			if (coprocessors != null && coprocessors.size() > 0) {
				return;
			} else {
				admin.disableTable(tableName);
				htd.addCoprocessor(coprocessorName);
				admin.modifyTable(tableName, htd);
				admin.enableTable(tableName);
			}
		} catch (TableNotFoundException e) {
			// TODO Auto-generated catch block
			log.error(e);
		} catch (MasterNotRunningException e) {
			// TODO Auto-generated catch block
			log.error(e);
		} catch (ZooKeeperConnectionException e) {
			// TODO Auto-generated catch block
			log.error(e);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			log.error(e);
		}
	}
}

Hbase 分佈查詢 - 應用後臺程式控制分頁
2015-12-19
HBase查詢優化
2018-08-05
優化
HBase學習
2019-04-14
最近學習了HBase
2019-07-14
hbase學習筆記
2018-03-13
筆記
（MySQL學習筆記）分頁查詢
2020-12-12
MySql筆記
HBase學習之一: 建立hive和hbase關聯表
2016-07-05
Hive
Phoenix：在HBase上執行SQL查詢
2015-01-23
SQL
Hbase學習二：Hbase資料特點和架構特點
2023-02-25
架構
HBase學習之Hbase的邏輯結構和物理結構
2021-01-02
HBase學習的第四天--HBase的進階與API
2024-08-16
API
學習六：安裝配置HBASE
2012-04-18
用HBase做高效能鍵值查詢？
2018-07-04
HBase 學習之一 <<HBase使用客戶端API動態建立Hbase資料表並在Hbase下匯出執行>>
2016-05-24
客戶端API
HBASE偽分佈搭建
2014-01-05
java實現Hbase中的查詢（一）Filter方式
2012-08-23
JavaFilter
Hadoop 學習之-HBase安裝
2015-04-23
Hadoop
查詢演算法之二分查詢
2020-10-09
演算法
Hbase(二)Hbase常用操作
2020-11-14
HBase查詢優化之Short-Circuit Local Reads
2018-08-12
優化UI
基於Solr的HBase多條件查詢測試
2014-10-15
Solr
Hbase學習筆記問題總結
2017-04-04
筆記
hadoop hive hbase 入門學習 (三)
2015-03-12
HadoopHive
力扣之二分查詢
2022-07-09
力扣
HBase學習的第五天--HBase進階結尾和phoenix開頭
2024-08-17
HBase 教程：什麼是 HBase？
2021-12-30
HBase篇--HBase常用優化
2018-01-16
優化
hbase - [04] java訪問hbase
2024-03-28
Java
菜鳥學Java（十）——分頁查詢
2013-08-21
Java
HBase高效能隨機查詢之道 – HFile原理解析
2018-07-04
隨機
HBase
2024-08-06
HBase篇--HBase操作Api和Java操作Hbase相關Api
2018-01-16
APIJava
Elasticsearch 分頁查詢
2021-04-05
Elasticsearch
ssh 分頁查詢
2018-01-30
oracle分頁查詢
2007-04-04
Oracle
HBase之四--(1)：Java操作Hbase進行建表、刪表以及對資料進行增刪改查，條件查詢...
2015-05-19
Java
HBase 系列（五）——HBase常用 Shell 命令
2021-09-09
Hbase單機部署 java連線Hbase
2020-11-09
Java

HBase學習之二: hbase分頁查詢

相關文章