Cai Li

54 papers A 1C 5Journal 30Unranked 18
YearRankTypeTitle / Venue / Authors
2026 — conf
BALANCE: Bit and Layer-Aware Lightweight ECC Design Method for In-Flash Computing Based LLM Inference Accelerator.
ASP-DAC
Changwei Yan, Lishuo Deng, Mingbo Hao, Cai Li, Weiwei Shan
2025 C conf
A 250M-2.5GHz Two-Stage Duty-Cycle Corrector with 10%-90% Correction Range and 3-Cycle Correction Latency for Mitigating Aging Effects.
ISCAS
Zhiting Li, Lishuo Deng, Cai Li, Changwei Yan, Zhangrui Qian, Weiwei Shan
2025 C conf
A 40nm Early-Warning AVFS Design with Path Activation-Based Monitoring Point Optimization.
ISCAS
Cai Li, Zhuoyuan Li, Kaize Zhou, Lishuo Deng, Junyi Qian, Xiaojie Yin, Kana Meng, Weiwei Shan
2025 J jnl
A Compound Timing Detection of Both Data Transition and Path Activation for Reliable In Situ Error Detection and Correction.
IEEE Trans. Very Large Scale Integr. Syst.
Lishuo Deng, Junyi Qian, Zhengguo Shen, Cai Li, Jingchen Wang, Zhangrui Qian, Ziyu Li, Longning Qi, Weiwei Shan
2025 J jnl
A Unified Early-Warning AVFS Design With Path Activation-Aware Monitoring Point Optimization.
IEEE Solid State Circuits Lett.
Cai Li, Kaize Zhou, Junyi Qian, Haochang Zhi, Weiwei Shan
2025 J jnl
An On-Chip-Training Keyword-Spotting Chip Using Interleaved Pipeline and Computation-in-Memory Cluster in 28-nm CMOS.
IEEE Trans. Very Large Scale Integr. Syst.
Junyi Qian, Cai Li, Long Chen, Ruidong Li, Tuo Li, Peng Cao, Xin Si, Weiwei Shan
2025 A conf
Efficient Hold Buffer Optimization by Supply Noise-Aware Dynamic Timing Analysis.
DATE
Lishuo Deng, Changwei Yan, Cai Li, Zhuo Chen, Weiwei Shan
2024 J jnl
A 0.61-μW Fully Integrated Keyword-Spotting ASIC With Real-Point Serial FFT-Based MFCC and Temporal Depthwise Separable CNN.
IEEE J. Solid State Circuits
Cai Li, Haochang Zhi, Kaiyue Yang, Junyi Qian, Zhihao Yan, Lixuan Zhu, Chao Chen, Xi Wang, Weiwei Shan
2024 — conf
A 22nm Variation-Tolerant Clock Duty-Cycle Controller for Mitigating Aging-Induced Clock Duty-Cycle Distortion with Path Replica.
ICTA
Lishuo Deng, Zhiting Li, Changwei Yan, Junyi Qian, Cai Li, Ruidong Li, Tuo Li, Weiwei Shan
2024 J jnl
A General Multilayer Analytical Radiative Transfer-Based Model for Reflectance Over Shallow Water.
IEEE Trans. Geosci. Remote. Sens.
Jilong Zhang, Zhantang Xu, Yongming Liu, Yuezhong Yang, Wen Zhou, Zeming Yang, Cai Li
2024 J jnl
A runtime-reconfigurable convolutional engine using tensor multiplication with multiple computing modes in 22-nm CMOS.
Microelectron. J.
Junyi Qian, Yuxin Ji, Cai Li
2024 — conf
An Adaptive Wide-Voltage-Range Droop Detection and Protection System Assisted with Timing Error Detection in 28nm CMOS.
CICC
Lishuo Deng, Zhengguo Shen, Zhuo Chen, Cai Li, Junyi Qian, Yuxuan Du, Kaize Zhou, Keran Li, Ruidong Li, Tuo Li, Xiaofeng Zou, Weiwei Shan
2023 J jnl
A 1.23μJ/Inference, All-Digital Shuffle-Type Group-CNN Accelerator in 28nm CMOS With 85.8% Accuracy on CIFAR-10.
IEEE Trans. Circuits Syst. II Express Briefs
Long Chen, Cai Li, Tao Wang, Junyi Qian, Weiwei Shan
2023 — conf
A 608nW Near-Microphone Keyword-Spotting Chip Using Real-Point Serial FFT-Based MFCC and Temporal Depthwise Separable CNN in 28nm CMOS.
CICC
Cai Li, Haochang Zhi, Long Chen, Kaiyue Yang, Junyi Qian, Zhihao Yan, Lixuan Zhu, Weiwei Shan
2023 J jnl
Study of the Profile Distribution of the Diffuse Attenuation Coefficient and Secchi Disk Depth in the Northwestern South China Sea.
Remote. Sens.
Xianqing Zhang, Cai Li, Wen Zhou, Yuanning Zheng, Wenxi Cao, Cong Liu, Zhantang Xu, Yuezhong Yang, Zeming Yang, Fei Chen
2022 J jnl
A generalizable deep learning framework for inferring fine-scale germline mutation rate maps.
Nat. Mac. Intell.
Yiyuan Fang, Shuyi Deng, Cai Li
2022 J jnl
Forecasting the Severity of COVID-19 Pandemic Amidst the Emerging SARS-CoV-2 Variants: Adoption of ARIMA Model.
Comput. Math. Methods Medicine
Cai Li, Agyemang Kwasi Sampene, Fredrick Oteng Agyeman, Brenya Robert, Abraham Lincoln Ayisi
2022 J jnl
Multiresolution Analysis Based on Dual-Scale Regression for Pansharpening.
IEEE Trans. Geosci. Remote. Sens.
Peng Wang, Hongyu Yao, Cai Li, Gong Zhang, Henry Leung
2022 J jnl
Subpixel Flood Inundation Mapping Based on Spatial-Spectral Information in Irregular Regions.
IEEE Geosci. Remote. Sens. Lett.
Peng Wang, Zhongchen He, Cai Li, Kang Ni
2022 J jnl
Super-taxon in human microbiome are identified to be associated with colorectal cancer.
BMC Bioinform.
Wei Dai, Cai Li, Ting Li, Jianchang Hu, Heping Zhang
2021 — conf
Effective Linewidth Reduction in Self-Homodyne Coherent Reception Enabled by stimulated Brillouin scattering.
OFC
Cai Li, Moritz Merklein, Yang Liu, Amol Choudhary, Benjamin J. Eggleton, Bill Corcoran
2021 J jnl
Kinematic-Based Classification of Social Gestures and Grasping by Humans and Machine Learning Techniques.
Frontiers Robotics AI
Paul Hemeren, Peter Veto, Serge Thill, Cai Li, Jiong Sun
2021 J jnl
Vertical Variability of Total and Size-Partitioned Phytoplankton Carbon in the South China Sea.
Remote. Sens.
Wendi Zheng, Wen Zhou, Wenxi Cao, Yupeng Liu, Guifen Wang, Lin Deng, Cai Li, Yu Zhang, Kai Zeng
2020 J jnl
Application of KPCA and AdaBoost algorithm in classification of functional magnetic resonance imaging of Alzheimer's disease.
Neural Comput. Appl.
Zhao Fan, Fanyu Xu, Cai Li, Lili Yao
2020 J jnl
Classification of Alzheimer's disease based on brain MRI and machine learning.
Neural Comput. Appl.
Zhao Fan, Fanyu Xu, Xuedan Qi, Cai Li, Lili Yao
2020 J jnl
Deep Learning-Based Security Verification for a Random Number Generator Using White Chaos.
Entropy
Cai Li, Jianguo Zhang, Luxiao Sang, Lishuang Gong, Longsheng Wang, Anbang Wang, Yuncai Wang
2020 J jnl
Development of an In Situ Analyzer Based on Sequential Injection Analysis and Liquid Waveguide Capillary Flow Cell for the Determination of Dissolved Reactive Phosphorus in Natural Waters.
Sensors
Zeming Yang, Cai Li, Zhenzhao Zhang, Guixin Lu, Zifeng Cai, Wenxi Cao
2020 — conf
Optical Labelling and Performance Monitoring in Coherent Optical Wavelength Division Multiplexing Networks.
OFC
Chao Yang, Xiang Li, Ming Luo, Zhixue He, Haibo Li, Cai Li, Shaohua Yu
2019 — conf
Demonstration of Bidirectional Real-Time 100 Gb/s (4×25 Gb/s) Coherent UDWDM-PON with Power Budget of 44 dB.
OFC
Ming Luo, DanYu Wu, Weizhong Li, Tao Zeng, Lei Zhou, Lingheng Meng, Zhixue He, Cai Li, Xiang Li
2019 — conf
Direct Detection of Pilot-Assisted PAM Signals for DCI and Metro Networks.
OFC
Cai Li, Chao Yang, Ming Luo, Zhixue He, Xiang Li, Shaohua Yu
2019 J jnl
Retrieving Phytoplankton Size Class from the Absorption Coefficient and Chlorophyll A Concentration Based on Support Vector Machine.
Remote. Sens.
Lin Deng, Wen Zhou, Wenxi Cao, Wendi Zheng, Guifen Wang, Zhantang Xu, Cai Li, Yuezhong Yang, Shuibo Hu, Wenjing Zhao
2018 — conf
114-Gb/s PDM-DMT Transmission Over 100-km SSMF with High Phase Noise Tolerance Using Kramers-Kronig Detection and Digital Carrier Regeneration.
ECOC
Cai Li, Ming Luo, Quan You, Xiang Li
2018 J jnl
Damage Detection in a Plate Structure based on FBG sensing Technique under a Single-Mode Lamb Wave.
Int. J. Online Eng.
Cai Li, Hunag Xiao, Zuo Xiaoqiong
2018 J jnl
Erratum to: Fast covariance estimation for sparse functional data.
Stat. Comput.
Luo Xiao, Cai Li, William Checkley, Ciprian M. Crainiceanu
2018 J jnl
Fast covariance estimation for sparse functional data.
Stat. Comput.
Luo Xiao, Cai Li, William Checkley, Ciprian M. Crainiceanu
2018 J jnl
Ultrasonic Excitation - Fiber Bragg Grating Sensing Technique for Damage Identification.
Int. J. Online Eng.
Cai Li, Xiaoguang Yue
2017 — conf
Digital OFDM-PON employing binary intensity modulation and direct detection channels.
OFC
Rong Hu, Cai Li, Haibo Li, Qi Yang, Ming Luo, Shaohua Yu, William Shieh
2016 C conf
Experimental demonstration of few-mode fiber transmission with self-homodyne coherent detection.
ICCC
Ying Qiu, Ming Luo, Shaohua Yu, Xiang Li, Rong Hu, Cai Li, Qi Yang
2016 C conf
Flexible coherent PON system based on cost-effective heterodyne detection of PDM-PAM-n signal.
ICCC
Cai Li, Ming Luo, Ying Qiu, Rong Hu, Qi Yang, Shaohu Yu
2016 — conf
Transmission of 4×28-Gb/s PAM-4 over 160-km single mode fiber using 10G-class DML and photodiode.
OFC
Xiang Li, Shiwei Zhou, Honglin Ji, Ming Luo, Qi Yang, Lilin Yi, Rong Hu, Cai Li, Songnian Fu, Arokiaswami Alphones, Wen-De Zhong, Changyuan Yu
2014 J jnl
A Novel Approach to Locomotion Learning: Actor-Critic Architecture using Central Pattern Generators and Dynamic Motor Primitives.
Frontiers Neurorobotics
Cai Li, Robert Lowe, Tom Ziemke
2014 — conf
Delayed self-homodyne detection for OFDM-PON downstream.
OFC
Rong Hu, Tao Gui, Zhaohui Li, Haibo Li, Chao Li, Cai Li, Xiao Xiao, Qi Yang, Shaohua Yu, Xi Chen, William Shieh
2013 — conf
Crawling Posture Learning in Humanoid Robots using a Natural-Actor-Critic CPG Architecture.
ECAL
Cai Li, Robert Lowe, Tom Ziemke
2013 J jnl
Effect of PAPR Reduction on Spectrum and Energy Efficiencies in OFDM Systems With Class-A HPA Over AWGN Channel.
IEEE Trans. Broadcast.
Tao Jiang, Cai Li, Chunxing Ni
2013 J jnl
Humanoids Learning to Walk: A Natural CPG-Actor-Critic Architecture.
Frontiers Neurorobotics
Cai Li, Robert Lowe, Tom Ziemke
2012 J jnl
An Instrument for
Sensors
Cai Li, Wenxi Cao, Jing Yu, Tiancun Ke, Guixin Lu, Yuezhong Yang, Chaoying Guo
2012 — conf
Modelling Walking Behaviors Based on CPGs: A Simplified Bio-inspired Architecture.
SAB
Cai Li, Robert Lowe, Tom Ziemke
2012 J jnl
Simple Alternative Multisequences for PAPR Reduction Without Side Information in SFBC MIMO-OFDM Systems.
IEEE Trans. Veh. Technol.
Tao Jiang, Cai Li
2011 J jnl
A Novel Constellation Reshaping Method for PAPR Reduction of OFDM Signals.
IEEE Trans. Signal Process.
Cai Li, Tao Jiang, Yang Zhou, Haibo Li
2011 — conf
A Sparse Algorithm of General Neural Network Model.
ICAIC (4)
Gong Wei, Cai Li
2008 — conf
A Study of Office Lighting and Indoor Daylight at Leeds.
CGIV/MCS
Cai Li, Guihua Cui, Ronnier Luo
2006 C conf
Groundwater Monitoring in China.
APWeb
Qingcheng He, Cai Li
2005 — conf
Simulation of quantum cellular automaton circuits based on genetic simulated annealing algorithm.
ISCAS (3)
Sen Wang, Cai Li
2004 — conf
A New Method for Assessing Gloss Based on Digital Imaging.
CGIV
Wei Ji, M. Ronnier Luo, Cai Li, Guihua Cui, Michael R. Pointer
notebooks/redb-clickhouse-TEST.ipynb
← Index notebooks/redb-clickhouse-TEST.ipynb plaintext
Truncated preview: showing the first 16384 bytes out of 5988848 total.
{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Definitions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import clickhouse_connect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "ch_TEST_client = clickhouse_connect.get_client(host='e.rationaledge.io', port=443, username='default', password='werioubTGwvf0-dsbv_', database='redb', verify=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "ch_TEST_BETA_client = clickhouse_connect.get_client(host='k.rationaledge.io', port=443, username='default', password='werioubTGwvf0-dsbv_', database='default', verify=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method HttpClient.close_connections of <clickhouse_connect.driver.httpclient.HttpClient object at 0x105fd64d0>>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ch_TEST_BETA_client.close_connections"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<clickhouse_connect.driver.summary.QuerySummary at 0x11f900810>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ch_TEST_BETA_client.command(\"\"\"\n",
    "RENAME DATABASE redb TO orig_redb;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DB Creation (schema 2025-08-02)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ch_TEST_client.command(\"\"\"\n",
    "CREATE DATABASE IF NOT EXISTS redb;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define General Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### CREATE FULL REDB DATABASE\n",
    "def create_redb_with_all_tables(client):\n",
    "    client.command(\"\"\"\n",
    "        CREATE DATABASE IF NOT EXISTS redb\n",
    "    \"\"\")\n",
    "    create_all_general_tables(client)\n",
    "    create_all_pe_tables(client)\n",
    "\n",
    "#### CREATE FULL TABLES\n",
    "def create_all_general_tables(client):\n",
    "    create_basic_properties_dbtable(client)\n",
    "    create_hashes_dbtable(client)\n",
    "    create_capa_dbtable(client)\n",
    "    create_die_dbtable(client)\n",
    "    create_extra_findings_dbtable(client)\n",
    "\n",
    "\n",
    "##################################\n",
    "#### CREATE INDIVIDUAL TABLES ####\n",
    "##################################\n",
    "\n",
    "#### GENERAL TABLES ####\n",
    "# All tables have SETTINGS index_granularity = 8192\n",
    "def create_basic_properties_dbtable(client):\n",
    "    # Create samples_metadata table\n",
    "    client.command(\"\"\"\n",
    "        CREATE TABLE IF NOT EXISTS redb_basic_properties (\n",
    "            sha256 FixedString(64), -- primary key\n",
    "            md5 FixedString(32),\n",
    "            sha1 FixedString(40),\n",
    "            filename Nullable(String),\n",
    "            sample_name String,\n",
    "            filesize UInt64,\n",
    "            file_entropy Float64,\n",
    "            filetype LowCardinality(String),\n",
    "            filetype_mime LowCardinality(String),\n",
    "            filetype_magika LowCardinality(String),\n",
    "            analysis_date DateTime64(3, 'UTC'),\n",
    "        ) ENGINE = ReplacingMergeTree(analysis_date)\n",
    "        PRIMARY KEY (sha256)\n",
    "        ORDER BY (sha256)\n",
    "    \"\"\")\n",
    "\n",
    "def create_hashes_dbtable(client):\n",
    "    # Create hashes table\n",
    "    client.command(\"\"\"\n",
    "        CREATE TABLE IF NOT EXISTS redb_hashes (\n",
    "            sha256 FixedString(64), -- primary key\n",
    "            md5 FixedString(32),\n",
    "            sha1 FixedString(40),\n",
    "            ssdeep_hash Nullable(String),\n",
    "            tlsh_hash Nullable(String),\n",
    "            authentihash Nullable(String),\n",
    "            imphash Nullable(String),\n",
    "            impfuzzy Nullable(String),\n",
    "            typerefhash Nullable(String),\n",
    "            richhash Nullable(String),\n",
    "            richpe_hash Nullable(String),\n",
    "            richpv_hash Nullable(String),\n",
    "            richpv_hash_sorted Nullable(String),\n",
    "            dhash Nullable(String),\n",
    "            analysis_date DateTime64(3, 'UTC')\n",
    "        ) ENGINE = ReplacingMergeTree(analysis_date)\n",
    "        PRIMARY KEY (sha256)\n",
    "        ORDER BY (sha256)\n",
    "    \"\"\")\n",
    "\n",
    "def create_capa_dbtable(client):\n",
    "    # Create sample_capabilities table\n",
    "    client.command(\"\"\"\n",
    "        SET enable_json_type = 1;\n",
    "    \"\"\")\n",
    "    client.command(\"\"\"\n",
    "        -- Base table for CAPA analysis results\n",
    "        CREATE TABLE IF NOT EXISTS redb_capa (\n",
    "            sha256 FixedString(64),\n",
    "            md5 FixedString(32),\n",
    "            sha1 FixedString(40),\n",
    "            analysis_date DateTime64(3, 'UTC'),\n",
    "            -- Store full JSON dumps as strings for flexibility\n",
    "            capa_dump JSON CODEC(ZSTD(3)),\n",
    "            attack_dump JSON CODEC(ZSTD(3)),\n",
    "            mbc_dump JSON CODEC(ZSTD(3))\n",
    "        ) ENGINE = ReplacingMergeTree(analysis_date)\n",
    "        ORDER BY sha256;\n",
    "    \"\"\")\n",
    "\n",
    "    client.command(\"\"\"\n",
    "        -- Normalized table for capabilities and namespaces\n",
    "        CREATE TABLE IF NOT EXISTS redb_capa_capabilities (\n",
    "            sha256 FixedString(64),\n",
    "            md5 FixedString(32),\n",
    "            sha1 FixedString(40),\n",
    "            analysis_date DateTime64(3, 'UTC'),\n",
    "            namespace LowCardinality(String),\n",
    "            capability LowCardinality(String)\n",
    "        ) ENGINE = ReplacingMergeTree(analysis_date)\n",
    "        ORDER BY (sha256, namespace, capability);\n",
    "    \"\"\")\n",
    "\n",
    "    client.command(\"\"\"\n",
    "        -- Materialized view for capability statistics\n",
    "        CREATE MATERIALIZED VIEW IF NOT EXISTS redb_capa_capabilities_stats (\n",
    "            namespace LowCardinality(String),\n",
    "            capability LowCardinality(String),\n",
    "            samples_bitmap AggregateFunction(groupBitmap, UInt64),\n",
    "            occurrence_count UInt64\n",
    "        ) ENGINE = AggregatingMergeTree()\n",
    "        ORDER BY (namespace, capability)\n",
    "        AS SELECT\n",
    "            namespace,\n",
    "            capability,\n",
    "            groupBitmapState(cityHash64(sha256)) as samples_bitmap,\n",
    "            count() as occurrence_count\n",
    "        FROM redb_capa_capabilities\n",
    "        GROUP BY namespace, capability;\n",
    "    \"\"\")\n",
    "\n",
    "    client.command(\"\"\"\n",
    "        -- Normalized table for MITRE ATT&CK mappings\n",
    "        CREATE TABLE IF NOT EXISTS redb_capa_attack (\n",
    "            sha256 FixedString(64),\n",
    "            md5 FixedString(32),\n",
    "            sha1 FixedString(40),\n",
    "            analysis_date DateTime64(3, 'UTC'),\n",
    "            tactic LowCardinality(String),\n",
    "            technique LowCardinality(String),\n",
    "            technique_id LowCardinality(String)\n",
    "        ) ENGINE = ReplacingMergeTree(analysis_date)\n",
    "        ORDER BY (sha256, tactic, technique_id);\n",
    "    \"\"\")\n",
    "\n",
    "    client.command(\"\"\"\n",
    "        -- Materialized view for ATT&CK statistics\n",
    "        CREATE MATERIALIZED VIEW IF NOT EXISTS redb_capa_attack_stats (\n",
    "            tactic LowCardinality(String),\n",
    "            technique LowCardinality(String),\n",
    "            technique_id LowCardinality(String),\n",
    "            samples_bitmap AggregateFunction(groupBitmap, UInt64),\n",
    "            occurrence_count UInt64\n",
    "        ) ENGINE = AggregatingMergeTree()\n",
    "        ORDER BY (tactic, technique_id)\n",
    "        AS SELECT\n",
    "            tactic,\n",
    "            technique,\n",
    "            technique_id,\n",
    "            groupBitmapState(cityHash64(sha256)) as samples_bitmap,\n",
    "            count() as occurrence_count\n",
    "        FROM redb_capa_attack\n",
    "        GROUP BY tactic, technique, technique_id;\n",
    "    \"\"\")\n",
    "\n",
    "    client.command(\"\"\"\n",
    "        -- Normalized table for MBC mappings\n",
    "        CREATE TABLE IF NOT EXISTS redb_capa_mbc (\n",
    "            sha256 FixedString(64),\n",
    "            md5 FixedString(32),\n",
    "            sha1 FixedString(40),\n",
    "            analysis_date DateTime64(3, 'UTC'),\n",
    "            objective LowCardinality(String),\n",
    "            behavior LowCardinality(String),\n",
    "            behavior_id LowCardinality(String)\n",
    "        ) ENGINE = ReplacingMergeTree(analysis_date)\n",
    "        ORDER BY (sha256, objective, behavior_id);\n",
    "    \"\"\")\n",
    "\n",
    "    client.command(\"\"\"\n",
    "        -- Materialized view for MBC statistics\n",
    "        CREATE MATERIALIZED VIEW IF NOT EXISTS redb_capa_mbc_stats (\n",
    "            objective LowCardinality(String),\n",
    "            behavior LowCardinality(String),\n",
    "            behavior_id LowCardinality(String),\n",
    "            samples_bitmap AggregateFunction(groupBitmap, UInt64),\n",
    "            occurrence_count UInt64\n",
    "        ) ENGINE = AggregatingMergeTree()\n",
    "        ORDER BY (objective, behavior_id)\n",
    "        AS SELECT\n",
    "            objective,\n",
    "            behavior,\n",
    "            behavior_id,\n",
    "            groupBitmapState(cityHash64(sha256)) as samples_bitmap,\n",
    "            count() as occurrence_count\n",
    "        FROM redb_capa_mbc\n",
    "        GROUP BY objective, behavior, behavior_id;\n",
    "    \"\"\")\n",
    "\n",
    "def create_die_dbtable(client):\n",
    "    # Create sample_die table\n",
    "    client.command(\"\"\"\n",
    "        SET enable_json_type = 1;\n",
    "    \"\"\")\n",
    "    client.command(\"\"\"\n",
    "        CREATE TABLE IF NOT EXISTS redb_die (\n",
    "            sha256 FixedString(64),\n",
    "            md5 FixedString(32),\n",
    "            sha1 FixedString(40),\n",
    "            die_info JSON CODEC(ZSTD(3)),\n",
    "            analysis_date DateTime64(3, 'UTC')\n",
    "        ) ENGINE = ReplacingMergeTree(analysis_date)\n",
    "        PRIMARY KEY (sha256)\n",
    "        ORDER BY (sha256)\n",
    "    \"\"\")\n",
    "\n",
    "    client.command(\"\"\"\n",
    "        CREATE MATERIALIZED VIEW redb_die_mv\n",
    "        (\n",
    "            sha256 FixedString(64),\n",
    "            md5 FixedString(32),\n",
    "            sha1 FixedString(40),\n",
    "            analysis_date DateTime64(3, 'UTC'),\n",
    "            compiler LowCardinality(Nullable(String)),\n",
    "            compiler_full Nullable(String),\n",
    "            format LowCardinality(Nullable(String)),\n",
    "            format_full Nullable(String),\n",
    "            installer LowCardinality(Nullable(String)),\n",
    "            installer_full Nullable(String),\n",
    "            joiner LowCardinality(Nullable(String)),\n",
    "            joiner_full Nullable(String),\n",
    "            language LowCardinality(Nullable(String)),\n",
    "            language_full Nullable(String),\n",
    "            library LowCardinality(Nullable(String)),\n",
    "            library_full Nullable(String),\n",
    "            linker LowCardinality(Nullable(String)),\n",
    "            linker_full Nullable(String),\n",
    "            operation_system LowCardinality(Nullable(String)),\n",
    "            operation_system_full Nullable(String),\n",
    "            packer LowCardinality(Nullable(String)),\n",
    "            packer_full Nullable(String),\n",
    "            protector LowCardinality(Nullable(String)),\n",
    "            protector_full Nullable(String),\n",
    "            sfx LowCardinality(Nullable(String)),\n",
    "            sfx_full Nullable(String),\n",
    "            sign_tool LowCardinality(Nullable(String)),\n",
    "            sign_tool_full Nullable(String),\n",
    "            stub LowCardinality(Nullable(String)),\n",
    "            stub_full Nullable(String),\n",
    "            tool LowCardinality(Nullable(String)),\n",
    "            tool_full Nullable(String),   \n",
    "            -- overlay JSON CODEC(ZSTD(3)),\n",
    "            -- resource JSON CODEC(ZSTD(3)),\n",
    "            has_overlay UInt8,\n",
    "            has_resources UInt8,\n",
    "            is_packed UInt8\n",
    "        )\n",
    "        ENGINE = ReplacingMergeTree(analysis_date)\n",
    "        ORDER BY (sha256)\n",
    "        POPULATE\n",
    "        AS SELECT\n",
    "            sha256,\n",
    "            md5,\n",
    "            sha1,\n",
    "            analysis_date,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'compiler'), '') AS compiler,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'compiler(full)'), '') AS compiler_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'format'), '') AS format,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'format(full)'), '') AS format_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'installer'), '') AS installer,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'installer(full)'), '') AS installer_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'joiner'), '') AS joiner,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'joiner(full)'), '') AS joiner_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'language'), '') AS language,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'language(full)'), '') AS language_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'library'), '') AS library,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'library(full)'), '') AS library_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'linker'), '') AS linker,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'linker(full)'), '') AS linker_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'operation_system'), '') AS operation_system,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'operation_system(full)'), '') AS operation_system_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'packer'), '') AS packer,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'packer(full)'), '') AS packer_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'protector'), '') AS protector,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'protector(full)'), '') AS protector_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'sfx'), '') AS sfx,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'sfx(full)'), '') AS sfx_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'sign_tool'), '') AS sign_tool,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'sign_tool(full)'), '') AS sign_tool_full,\n",
    "            nullIf(JSONExtractString(toString(die_info), 'stub'), '') AS stub,\n",
    "            nullIf(JSONExtractString(toString(die_info),