Xiaohong Yang

52 papers A 2C 2Journal 30Unranked 18
YearRankTypeTitle / Venue / Authors
2026 J jnl
ELSA: Efficient LLM-Centric Split Aggregation for Privacy-Aware Hierarchical Federated Learning over Resource-Constrained Edge Networks.
CoRR
Xiaohong Yang, Tong Xie, Minghui Liwang, Chikai Shang, Yang Lu, Zhenzhen Jiao, Liqun Fu, Seyyedali Hosseinalipour
2026 J jnl
Energy consumption optimization algorithm for distributed data in data center based on multimodal adaptive redundancy elimination.
Sustain. Comput. Informatics Syst.
Xin Li, Xiaohong Yang, Qinghui Zeng, Zhitong Liu
2026 J jnl
RE-MCDF: Closed-Loop Multi-Expert LLM Reasoning for Knowledge-Grounded Clinical Diagnosis.
CoRR
Shaowei Shen, Xiaohong Yang, Jie Yang, Lianfen Huang, Yongcai Zhang, Yang Zou, Seyyedali Hosseinalipour
2025 J jnl
A cross population study of retinal aging biomarkers with longitudinal pre-training and label distribution learning.
npj Digit. Medicine
Zhen Yu, Ruiye Chen, Peng Gui, Wei Wang, Imran Razzak, Hamid Alinejad-Rokny, Xiaomin Zeng, Xianwen Shang, Lei Zhang, Xiaohong Yang, Honghua Yu, Wenyong Huang, Huimin Lu, Peter van Wijngaarden, Mingguang He, Zhuoting Zhu, Zongyuan Ge
2025 J jnl
Adaptive UAV-Assisted Hierarchical Federated Learning: Optimizing Energy, Latency, and Resilience for Dynamic Smart IoT.
CoRR
Xiaohong Yang, Minghui Liwang, Liqun Fu, Yuhan Su, Seyyedali Hosseinalipour, Xianbin Wang, Yiguang Hong
2025 J jnl
Adaptive UAV-Assisted Hierarchical Federated Learning: Optimizing Energy, Latency, and Resilience for Dynamic Smart IoT.
IEEE Trans. Serv. Comput.
Xiaohong Yang, Minghui Liwang, Liqun Fu, Yuhan Su, Seyyedali Hosseinalipour, Xianbin Wang, Yiguang Hong
2025 J jnl
HEART: Achieving Timely Multi-Model Training for Vehicle-Edge-Cloud-Integrated Hierarchical Federated Learning.
CoRR
Xiaohong Yang, Minghui Liwang, Xianbin Wang, Zhipeng Cheng, Seyyedali Hosseinalipour, Huaiyu Dai, Zhenzhen Jiao
2024 J jnl
A Comparison of the Validity of Online Courses Quality Evaluation Models.
Int. J. Web Based Learn. Teach. Technol.
Jingwen Wang, Xiaohong Yang, Dujuan Liu
2024 J jnl
Carbon emission accounting and decarbonization strategies in museum industry.
Energy Inform.
Yan Bai, Xiaohong Yang, Lan Zhang, Rui Zhang, Nan Chen, Xiaojuan Dai
2024 J jnl
Refined Water-Body Types Mapping Using a Water-Scene Enhancement Deep Models by Fusing Optical and SAR Images.
IEEE J. Sel. Top. Appl. Earth Obs. Remote. Sens.
Haozheng Ma, Xiaohong Yang, Runyu Fan, Wei Han, Kang He, Lizhe Wang
2023 J jnl
A Comparative Study of the Temperature Change in a Warm Eddy Using Multisource Data.
Remote. Sens.
Xiaohong Yang, Yanming Yang, Jinbao Weng
2023 J jnl
A spectral decomposition method for estimating the leaf nitrogen status of maize by UAV-based hyperspectral imaging.
Comput. Electron. Agric.
Meiyan Shu, Jinyu Zhu, Xiaohong Yang, Xiaohe Gu, Baoguo Li, Yuntao Ma
2023 C conf
DMAM-UNET:An Improved Unet Semantic Segmentation for Water Body Extraction from Remotely Sensed Image.
IGARSS
Zirong Liu, Xinlong Liu, Menghui Yu, Xiaohong Yang
2023 — conf
Design and Research of Smart Sluice Station Based on GIS and BIM.
Geoinformatics
Zilin Li, Xinlong Liu, Xiaohong Yang
2023 J jnl
Evaluation and Projection of Precipitation in CMIP6 Models over the Qilian Mountains, China.
Remote. Sens.
Xiaohong Yang, Weijun Sun, Jiake Wu, Jiahang Che, Mengyuan Liu, Qinglin Zhang, Yingshan Wang, Baojuan Huai, Yuzhe Wang, Lei Wang
2023 J jnl
Velocity Vector Estimation of Two-Dimensional Flow Field Based on STIV.
Sensors
Jianghuai Lu, Xiaohong Yang, Jianping Wang
2022 J jnl
An automatic framework for perioperative risks classification from retinal images of complex congenital heart disease patients.
Int. J. Mach. Learn. Cybern.
Wing W. Y. Ng, Haicong Liang, Qingsheng Peng, Cankun Zhong, Xinran Dong, Zhongning Huang, Pingting Zhong, Cong Li, Minghui Xu, Yunxia Sun, Honghua Yu, Xiaohong Yang
2022 J jnl
Improved estimation of canopy water status in maize using UAV-based digital and hyperspectral images.
Comput. Electron. Agric.
Meiyan Shu, Qizhou Wang, Shuaipeng Fei, Xiaohong Yang, Zhu Jinyu, Meng Lei, Baoguo Li, Yuntao Ma
2022 J jnl
SADA-Net: A Shape Feature Optimization and Multiscale Context Information-Based Water Body Extraction Method for High-Resolution Remote Sensing Images.
IEEE J. Sel. Top. Appl. Earth Obs. Remote. Sens.
Bin Wang, Zhanlong Chen, Liang Wu, Xiaohong Yang, Yuan Zhou
2022 J jnl
Water Body Super-Resolution Mapping Based on Multiple Endmember Spectral Mixture Analysis and Multiscale Spatio-Temporal Dependence.
Remote. Sens.
Xiaohong Yang, Qiannian Chu, Lizhe Wang, Menghui Yu
2020 J jnl
Spatio-Temporal Sub-Pixel Land Cover Mapping of Remote Sensing Imagery Using Spatial Distribution Information From Same-Class Pixels.
Remote. Sens.
Xiaodong Li, Rui Chen, Giles M. Foody, Lihui Wang, Xiaohong Yang, Yun Du, Feng Ling
2019 J jnl
Assessment of Binocular Imbalance with an Augmented Virtual Reality Platform in a Normal Population.
Cyberpsychology Behav. Soc. Netw.
Li Xu, Manqing Huang, Jianqing Lan, Wanshu Huang, Xuelai Wang, Guanrong Zhang, Xue Li, Pang Shasha, Hang Chu, Brenda K. Wiederhold, Mark D. Wiederhold, Li Yan, Xiaohong Yang, Jin Zeng
2019 J jnl
Optimal Endmember-Based Super-Resolution Land Cover Mapping.
IEEE Geosci. Remote. Sens. Lett.
Xinyan Li, Xiaodong Li, Giles M. Foody, Xiaohong Yang, Yihang Zhang, Yun Du, Feng Ling
2019 J jnl
Uncovering cortical activations of discourse comprehension and their overlaps with common large-scale neural networks.
NeuroImage
Xiaohong Yang, Huijie Li, Nan Lin, Xiuping Zhang, Yinshan Wang, Ying Zhang, Qian Zhang, Xi-Nian Zuo, Yufang Yang
2018 J jnl
Intelligent encryption algorithm for network communication parallel data of information release terminal.
J. Intell. Fuzzy Syst.
Xiaohong Yang, Donghong Yang, S. Aue
2018 — conf
Learning-Based Super-Resolution Land Cover Mapping with Additional Transformed Examples.
Geoinformatics
Xiaohong Yang, Zhong Xie, Mailing Song
2018 J jnl
Spatio-Temporal Super-Resolution Land Cover Mapping Based on Fuzzy C-Means Clustering.
Remote. Sens.
Xiaohong Yang, Zhong Xie, Feng Ling, Xiaodong Li, Yihang Zhang, Ming Zhong
2016 J jnl
Completing tags by local learning: a novel image tag completion method based on neighborhood tag vector predictor.
Neural Comput. Appl.
Xiaohong Yang, Feng Yang
2015 — conf
A middleware: Python plugin transform on different GIS platforms.
Geoinformatics
Zhuoran Pan, Xiaohong Yang, Zhong Xie
2015 — conf
Building damage assessment approach based on seismic spatial information grid.
Geoinformatics
Xiaohong Yang, Zhong Xie, Xiangang Luo, Zhanlong Chen
2015 C conf
Image Tag Completion by Local Learning.
ISNN
Jingyan Wang, Yihua Zhou, Haoxiang Wang, Xiaohong Yang, Feng Yang, Austin Peterson
2015 J jnl
Image tag completion by local learning.
CoRR
Jingyan Wang, Yihua Zhou, Haoxiang Wang, Xiaohong Yang, Feng Yang, Austin Peterson
2015 — conf
Rapid loss assessment of population in seismic disasters.
Geoinformatics
Lei Xue, Xiaohong Yang, Zhong Xie
2014 — conf
A Review of Research Progress on Direct Selling in China Mainland and Taiwan.
WHICEB
Xiaohong Yang, Jing Ling
2014 — conf
Characteristics of Direct Distributors' Consumption Behavior and How do they Influence on the Finance.
WHICEB
Xiaohong Yang, Qiuxia Xu
2014 J jnl
Predictors of Pause Duration in Read-Aloud Discourse.
IEICE Trans. Inf. Syst.
Xiaohong Yang, Mingxing Xu, Yufang Yang
2014 — conf
The simulation of direct torque control fuzzy control system based on Labview and Matlab.
ICSAI
Baodong Gao, Jianping Wang, Fan Cheng, Guo Zhang, Xiaohong Yang, Junhui Liu
2013 — conf
How Do Emotional Cues Modulate Readers' Perception of Emotional Arousal during Text Comprehension: An ERP Study.
BICS
Jinlu Zhang, Xiaohong Yang, Yufang Yang
2013 — conf
Seismic Spatial Information Grid: Applications of Geo-Informatics in Earthquake Disaster Management.
GRMSE (2)
Xiaohong Yang, Qiuwen Zhang
2012 J jnl
Prosodic Realization of Rhetorical Structure in Chinese Discourse.
IEEE Trans. Speech Audio Process.
Xiaohong Yang, Yufang Yang
2011 J jnl
A Dictionary Based Indexing Approach for Music Information Retrieval.
Int. J. Comput. Process. Orient. Lang.
Xiaohong Yang, Qingcai Chen, Xiaolong Wang
2011 A conf
Deep Belief Networks for Automatic Music Genre Classification.
INTERSPEECH
Xiaohong Yang, Qingcai Chen, Shusen Zhou, Xiaolong Wang
2011 — conf
The Research on Knowledge Management of Construction Enterprise.
ICEIS (4)
Xiaohong Yang
2011 — conf
Using Hybrid Kernel Method for Question Classification in CQA.
ICONIP (3)
Shixi Fan, Xiaolong Wang, Xuan Wang, Xiaohong Yang
2010 — conf
A Novel Approach Based on Fault Tolerance and Recursive Segmentation to Query by Humming.
AST/UCMA/ISA/ACN
Xiaohong Yang, Qingcai Chen, Xiaolong Wang
2010 — conf
Simulation of maximum wind energy tracking based on fuzzy control.
FSKD
Xiaohong Yang, Tong Yang
2008 — conf
A Coevolutionary Algorithm with Dimension Extraction.
ICNC (1)
Liping Yang, Xiaohong Yang, Houkuan Huang
2008 A conf
Adaptive filter based prosody modification approach.
INTERSPEECH
Qingcai Chen, Shusen Zhou, Dandan Wang, Xiaohong Yang
2008 — conf
Enterprise E-Business Transformation: A Case Study in China.
ISECS
Luchuan Liu, Jing Lu, Xiaohong Yang
2007 — conf
An Efficient Pareto-Coevolution Archive.
ICNC (4)
Liping Yang, Houkuan Huang, Xiaohong Yang
2006 — conf
A Reuse-Oriented Process Component Representation Framework.
Software Engineering Research and Practice
Xiaohong Yang, Jing Lu, Ruzhi Xu, Guangfeng Pan, Jin Liu
1998 J jnl
A CORBA-based agent-driven design for distributed intelligent manufacturing systems.
J. Intell. Manuf.
Ming Lei, Xiaohong Yang, Mitchell M. Tseng, Shuzi Yang
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),