Category Archives: 資料庫

Delete snapshots older than 7 days

Posted on 2023 年 11 月 16 日 by cowman.chiang

To avoid running out of disk space in our test environment, we developed a plan to regularly execute shell scripts to clean up unnecessary snapshots.

#!/bin/bash

week=`date --date='7 days ago' +'%Y%m%d'`
echo "list_snapshots" | /home/webuser/hbase-1.2.9/bin/hbase shell | grep "pattern " | \
while read CMD; do
    filename=($CMD)
#    echo $filename
    date=`echo $filename | awk -F "_" '{print $2}'`
#    echo  "${filename#*_}"
#    echo $date
#    echo ${date:0:8}
    if [ "${date:0:8}" -lt $week ]
    then
        echo "delete_snapshot '$filename'" | /home/webuser/hbase-1.2.9/bin/hbase shell
    fi
done

#!/bin/bash

week=`date --date='7 days ago' +'%Y%m%d'`

echo "list_snapshots" | /home/webuser/hbase-1.2.9/bin/hbase shell | grep "pattern " | \

while read CMD; do

filename=($CMD)

# echo $filename

date=`echo $filename | awk -F "_" '{print $2}'`

# echo "${filename#*_}"

# echo $date

# echo ${date:0:8}

if [ "${date:0:8}" -lt $week ]

then

echo "delete_snapshot '$filename'" | /home/webuser/hbase-1.2.9/bin/hbase shell

done

Posted in HBase, Shell Script | Comments Off

HBase Client 2.5.5 in JDK 11

Posted on 2023 年 10 月 02 日 by cowman.chiang

Because of the vulnerability scan results of the project, the JDK version must be upgraded from 8 to 11 to complete the repair. The original HBase Client version 1.2 we used was incompatible with JDK 11, so we had to … Continue reading →

Posted in HBase, Java | Comments Off

RocksDB Tool

Posted on 2022 年 10 月 07 日 by cowman.chiang

Install rocksdb first.

brew install rocksdb

1 2	brew install rocksdb

Add alias in zshrc

alias ldb = 'rocksdb_ldb --db=. '

1 2	alias ldb = 'rocksdb_ldb --db=. '

List all column families

# ldb list_column_families
Column families in .:
{default, S1, User, C1, C2, U1, C3}

# ldb list_column_families

Column families in .:

{default, S1, User, C1, C2, U1, C3}

Scan command:

# ldb --column_family=User scan
User_U_name : cowman
User_U_status : 0
User_U_type : 0
User_U_updatedTimestamp :
User_U_userId : U

# ldb --column_family=User scan

User_U_name : cowman

User_U_status : 0

User_U_type : 0

User_U_updatedTimestamp :

User_U_userId : U

Show result with hex value

ldb --column_family=User scan --value_hex
User_U_name : 0x636F776D616E
User_U_status : 0x30
User_U_type : 0x30
User_U_updatedTimestamp : 0x0000000000000000
User_U_userId : 0x55

ldb --column_family=User scan --value_hex

User_U_name : 0x636F776D616E

User_U_status : 0x30

User_U_type : 0x30

User_U_updatedTimestamp : 0x0000000000000000

User_U_userId : 0x55

Command description (Some commands are inconsistent with how they are used on linux servers)

ldb - RocksDB Tool

commands MUST specify --db=&lt;full_path_to_db_directory&gt; when necessary

commands can optionally specify
  --env_uri=&lt;uri_of_environment&gt; or --fs_uri=&lt;uri_of_filesystem&gt; if necessary
  --secondary_path=&lt;secondary_path&gt; to open DB as secondary instance. Operations not supported in secondary instance will fail.

The following optional parameters control if keys/values are input/output as hex or as plain strings:
  --key_hex : Keys are input/output as hex
  --value_hex : Values are input/output as hex
  --hex : Both keys and values are input/output as hex

The following optional parameters control the database internals:
  --column_family=&lt;string&gt; : name of the column family to operate on. default: default column family
  --ttl with 'put','get','scan','dump','query','batchput' : DB supports ttl and value is internally timestamp-suffixed
  --try_load_options : Try to load option file from DB. Default to true if db is specified and not creating a new DB and not open as TTL DB. Can be set to false explicitly.
  --disable_consistency_checks : Set options.force_consistency_checks = false.
  --ignore_unknown_options : Ignore unknown options when loading option file.
  --bloom_bits=&lt;int,e.g.:14&gt;
  --fix_prefix_len=&lt;int,e.g.:14&gt;
  --compression_type=&lt;no|snappy|zlib|bzip2|lz4|lz4hc|xpress|zstd&gt;
  --compression_max_dict_bytes=&lt;int,e.g.:16384&gt;
  --block_size=&lt;block_size_in_bytes&gt;
  --auto_compaction=&lt;true|false&gt;
  --db_write_buffer_size=&lt;int,e.g.:16777216&gt;
  --write_buffer_size=&lt;int,e.g.:4194304&gt;
  --file_size=&lt;int,e.g.:2097152&gt;
  --enable_blob_files : Enable key-value separation using BlobDB
  --min_blob_size=&lt;int,e.g.:2097152&gt;
  --blob_file_size=&lt;int,e.g.:2097152&gt;
  --blob_compression_type=&lt;no|snappy|zlib|bzip2|lz4|lz4hc|xpress|zstd&gt;
  --enable_blob_garbage_collection : Enable blob garbage collection
  --blob_garbage_collection_age_cutoff=&lt;double,e.g.:0.25&gt;
  --blob_garbage_collection_force_threshold=&lt;double,e.g.:0.25&gt;
  --blob_compaction_readahead_size=&lt;int,e.g.:2097152&gt;


Data Access Commands:
  put &lt;key&gt; &lt;value&gt; [--create_if_missing] [--ttl]
  get &lt;key&gt; [--ttl]
  batchput &lt;key&gt; &lt;value&gt; [&lt;key&gt; &lt;value&gt;] [..] [--create_if_missing] [--ttl]
  scan [--from] [--to]  [--ttl] [--timestamp] [--max_keys=&lt;N&gt;q]  [--start_time=&lt;N&gt;:- is inclusive] [--end_time=&lt;N&gt;:- is exclusive] [--no_value]
  delete &lt;key&gt;
  deleterange &lt;begin key&gt; &lt;end key&gt;
  query [--ttl]
    Starts a REPL shell.  Type help for list of available commands.
  approxsize [--from] [--to]
  checkconsistency
  list_file_range_deletes [--max_keys=&lt;N&gt;] : print tombstones in SST files.


Admin Commands:
  dump_wal --walfile=&lt;write_ahead_log_file_path&gt; [--header]  [--print_value]  [--write_committed=true|false]
  compact [--from] [--to]
  reduce_levels --new_levels=&lt;New number of levels&gt; [--print_old_levels]
  change_compaction_style --old_compaction_style=&lt;Old compaction style: 0 for level compaction, 1 for universal compaction&gt; --new_compaction_style=&lt;New compaction style: 0 for level compaction, 1 for universal compaction&gt;
  dump [--from] [--to]  [--ttl] [--max_keys=&lt;N&gt;] [--timestamp] [--count_only] [--count_delim=&lt;char&gt;] [--stats] [--bucket=&lt;N&gt;] [--start_time=&lt;N&gt;:- is inclusive] [--end_time=&lt;N&gt;:- is exclusive] [--path=&lt;path_to_a_file&gt;] [--decode_blob_index] [--dump_uncompressed_blobs]
  load [--create_if_missing] [--disable_wal] [--bulk_load] [--compact]
  manifest_dump [--verbose] [--json] [--path=&lt;path_to_manifest_file&gt;]
  update_manifest [--update_temperatures]      MUST NOT be used on a live DB.
  file_checksum_dump [--path=&lt;path_to_manifest_file&gt;]
  get_property &lt;property_name&gt;
  list_column_families
  create_column_family --db=&lt;db_path&gt; &lt;new_column_family_name&gt;
  drop_column_family --db=&lt;db_path&gt; &lt;column_family_name_to_drop&gt;
  dump_live_files [--decode_blob_index]  [--dump_uncompressed_blobs]
  idump [--from] [--to]  [--input_key_hex] [--max_keys=&lt;N&gt;] [--count_only] [--count_delim=&lt;char&gt;] [--stats] [--decode_blob_index]
  list_live_files_metadata [--sort_by_filename]
  repair [--verbose]
  backup [--backup_env_uri | --backup_fs_uri]  [--backup_dir]  [--num_threads]  [--stderr_log_level=&lt;int (InfoLogLevel)&gt;]
  restore [--backup_env_uri | --backup_fs_uri]  [--backup_dir]  [--num_threads]  [--stderr_log_level=&lt;int (InfoLogLevel)&gt;]
  checkpoint [--checkpoint_dir]
  write_extern_sst &lt;output_sst_path&gt;
  ingest_extern_sst &lt;input_sst_path&gt; [--move_files]  [--snapshot_consistency]  [--allow_global_seqno]  [--allow_blocking_flush]  [--ingest_behind]  [--write_global_seqno]
  unsafe_remove_sst_file &lt;SST file number        MUST NOT be used on a live DB.

ldb - RocksDB Tool

commands MUST specify --db=<full_path_to_db_directory> when necessary

commands can optionally specify

--env_uri=<uri_of_environment> or --fs_uri=<uri_of_filesystem> if necessary

--secondary_path=<secondary_path> to open DB as secondary instance. Operations not supported in secondary instance will fail.

The following optional parameters control if keys/values are input/output as hex or as plain strings:

--key_hex : Keys are input/output as hex

--value_hex : Values are input/output as hex

--hex : Both keys and values are input/output as hex

The following optional parameters control the database internals:

--column_family=<string> : name of the column family to operate on. default: default column family

--ttl with 'put','get','scan','dump','query','batchput' : DB supports ttl and value is internally timestamp-suffixed

--try_load_options : Try to load option file from DB. Default to true if db is specified and not creating a new DB and not open as TTL DB. Can be set to false explicitly.

--disable_consistency_checks : Set options.force_consistency_checks = false.

--ignore_unknown_options : Ignore unknown options when loading option file.

--bloom_bits=<int,e.g.:14>

--fix_prefix_len=<int,e.g.:14>

--compression_max_dict_bytes=<int,e.g.:16384>

--block_size=<block_size_in_bytes>

--auto_compaction=<true|false>

--db_write_buffer_size=<int,e.g.:16777216>

--write_buffer_size=<int,e.g.:4194304>

--file_size=<int,e.g.:2097152>

--enable_blob_files : Enable key-value separation using BlobDB

--min_blob_size=<int,e.g.:2097152>

--blob_file_size=<int,e.g.:2097152>

--enable_blob_garbage_collection : Enable blob garbage collection

--blob_garbage_collection_age_cutoff=<double,e.g.:0.25>

--blob_garbage_collection_force_threshold=<double,e.g.:0.25>

--blob_compaction_readahead_size=<int,e.g.:2097152>

Data Access Commands:

put <key> <value> [--create_if_missing] [--ttl]

get <key> [--ttl]

batchput <key> <value> [<key> <value>] [..] [--create_if_missing] [--ttl]

scan [--from] [--to] [--ttl] [--timestamp] [--max_keys=<N>q] [--start_time=<N>:- is inclusive] [--end_time=<N>:- is exclusive] [--no_value]

delete <key>

deleterange <begin key> <end key>

query [--ttl]

Starts a REPL shell. Type help for list of available commands.

approxsize [--from] [--to]

checkconsistency

list_file_range_deletes [--max_keys=<N>] : print tombstones in SST files.

Admin Commands:

dump_wal --walfile=<write_ahead_log_file_path> [--header] [--print_value] [--write_committed=true|false]

compact [--from] [--to]

reduce_levels --new_levels=<New number of levels> [--print_old_levels]

change_compaction_style --old_compaction_style=<Old compaction style: 0 for level compaction, 1 for universal compaction> --new_compaction_style=<New compaction style: 0 for level compaction, 1 for universal compaction>

dump [--from] [--to] [--ttl] [--max_keys=<N>] [--timestamp] [--count_only] [--count_delim=<char>] [--stats] [--bucket=<N>] [--start_time=<N>:- is inclusive] [--end_time=<N>:- is exclusive] [--path=<path_to_a_file>] [--decode_blob_index] [--dump_uncompressed_blobs]

load [--create_if_missing] [--disable_wal] [--bulk_load] [--compact]

manifest_dump [--verbose] [--json] [--path=<path_to_manifest_file>]

update_manifest [--update_temperatures] MUST NOT be used on a live DB.

file_checksum_dump [--path=<path_to_manifest_file>]

get_property <property_name>

list_column_families

create_column_family --db=<db_path> <new_column_family_name>

drop_column_family --db=<db_path> <column_family_name_to_drop>

dump_live_files [--decode_blob_index] [--dump_uncompressed_blobs]

idump [--from] [--to] [--input_key_hex] [--max_keys=<N>] [--count_only] [--count_delim=<char>] [--stats] [--decode_blob_index]

list_live_files_metadata [--sort_by_filename]

repair [--verbose]

backup [--backup_env_uri | --backup_fs_uri] [--backup_dir] [--num_threads] [--stderr_log_level=<int (InfoLogLevel)>]

restore [--backup_env_uri | --backup_fs_uri] [--backup_dir] [--num_threads] [--stderr_log_level=<int (InfoLogLevel)>]

checkpoint [--checkpoint_dir]

write_extern_sst <output_sst_path>

ingest_extern_sst <input_sst_path> [--move_files] [--snapshot_consistency] [--allow_global_seqno] [--allow_blocking_flush] [--ingest_behind] [--write_global_seqno]

unsafe_remove_sst_file <SST file number MUST NOT be used on a live DB.

Posted in RocksDB | Comments Off

[HBase] security issue

Posted on 2019 年 03 月 18 日 by cowman.chiang

error message: com.a.b.c.exception.BaseDAOException: org.apache.hadoop.hbase.security.AccessDeniedException: org.apache.hadoop.hbase.security.AccessDeniedException: Insufficient permissions for user ‘Cowman’ (table=ABC, action=READ) in jvm conf. add “-DHADOOP_USER_NAME=webuser”

Posted in HBase, Java | Leave a comment

[HBase] convert long value to bytes

Posted on 2018 年 11 月 13 日 by cowman.chiang

Java code:

public static void main(String[] args) {
    String stringValue = Bytes.toStringBinary(Bytes.toBytes(1532080782183l));
    System.out.println(stringValue);
    Long longValue = Bytes.toLong(Bytes.toBytesBinary(stringValue));
    System.out.println(longValue);
}

public static void main(String[] args) {

String stringValue = Bytes.toStringBinary(Bytes.toBytes(1532080782183l));

System.out.println(stringValue);

Long longValue = Bytes.toLong(Bytes.toBytesBinary(stringValue));

System.out.println(longValue);

}

HBase shell command:

hbase(main):056:0> Bytes.toStringBinary(Bytes.to_bytes(1532080782183))
=> "\\x00\\x00\\x01d\\xB7!{g"
hbase(main):057:0> Bytes.toLong("\x00\x00\x01d\xB7!{g".to_java_bytes)
=> 1532080782183

hbase(main):056:0> Bytes.toStringBinary(Bytes.to_bytes(1532080782183))

=> "\\x00\\x00\\x01d\\xB7!{g"

hbase(main):057:0> Bytes.toLong("\x00\x00\x01d\xB7!{g".to_java_bytes)

=> 1532080782183

Posted in HBase, Java | Leave a comment

[HBase] Filters not working for negative integers

Posted on 2018 年 07 月 12 日 by cowman.chiang

[stackoverflow] HBase: Filters not working for negative integers Easility say: Since, Hbase has only BinaryComparators and not other ‘typed’ comparators, it fails to filter on Negative integers as it stores the 2’s compliment of the negative number. Further, the binary … Continue reading →

Posted in HBase | Leave a comment

[HBase] get the scan result without specific cq

Posted on 2018 年 06 月 06 日 by cowman.chiang

scan ‘TableName’, FILTER=>”QualifierFilter(!=,’binary:QUALIFY2′)”

Posted in HBase | Leave a comment

Install Hbase 5.9 in Mac OS X

Posted on 2017 年 03 月 10 日 by cowman.chiang

Download hbase package tar.gz file from https://www.cloudera.com/documentation/enterprise/release-notes/topics/cdh_vd_cdh_package_tarball_59.html Untar tar.gz file edit conf/hbase-env.sh export JAVA_HOME={{JAVA_HOME Directory path}} edit conf/hbase-site.xml

  <property>
    <name>hbase.rootdir</name>
    <value>file:///{{location}}/data</value>
  </property>
  <property>
    <name>hbase.zookeeper.property.dataDir</name>
    <value>{{location}}/zookeeper</value>
  </property>
  <property>
    <name>hbase.zookeeper.quorum</name>
    <value>{{hostname}}</value>
  </property>

<name>hbase.rootdir</name>

<value>file:///{{location}}/data</value>

</property>

<name>hbase.zookeeper.property.dataDir</name>

<value>{{location}}/zookeeper</value>

</property>

<name>hbase.zookeeper.quorum</name>

<value>{{hostname}}</value>

</property>

start hbase service bin/start-hbase.sh run hbase shell bin/hbase shell stop hbase shell service bin/stop-hbase.sh

Posted in HBase, Mac | Leave a comment

HBase: put byte value

Posted on 2016 年 10 月 04 日 by cowman.chiang

put “TableName”, “rowkey”, “cf:fieldname”, [0].pack(“N”) N => byte array put “TableName”, “rowkey”, “cf:fieldname”, [0].pack(“Q>”) Q => 64-bit unsigned, native endian => change endian to big endian

Posted in HBase | Leave a comment

[MySQL] Find in fixed string

Posted on 2016 年 05 月 27 日 by cowman.chiang

Ref. MySQL 的 FIND_IN_SET函數 SELECT * FROM table WHERE FIND_IN_SET(ID, ‘2,5,6,7,8,9,11,21,33,45’)

Posted in MySQL | Leave a comment

Category Archives: 資料庫

Categories