Power BI 是微软开发的一款功能强大的商业智能工具。 它使用户能够可视化和分析数据，以做出明智的业务决策。 用户可以创建交互式和可定制的仪表板、报告和可视化。

前提条件 #

1. 操作系统与环境要求：
   1. 仅支持 64 位 (x64) 平台的 Windows 11，Windows 10, Windows Server 2012 R2, Windows Server 2012, Windows 8, Windows 8.1, Windows Server 2016, Windows Server 2019；
   2. 系统使用 Internet Explorer 10 或更高版本。
2. 安装 ClickHouse ODBC Driver v1.2.1.20220905 版本，您可以从 这里 获取安装程序。
3. 安装 Power BI Gateway。请参考官方文档安装。
4. 安装 ClickHouse 社区的 power-bi-clickhouse Connector 来进行数据分析。请参考官方文档安装 ClickHouse ODBC Driver 和 power-bi-clickhouse。
5. 拥有 ByteHouse 账号。

配置与使用 #

1. 启用 Custom connector。在 PowerBI Cloud 中启用 power-bi-clickhouse，需要 enable custom connector。对于 Custom Connectors 的文件夹需要赋予 Remote access 权限，默认赋予的对象是 NT SERVICE\PBIEgwService.

   
   

2. 验证 Custom Connector 是否成功。如果 Connectors 中没有出现这个信息，则需要尝试重启 Gateway。

   

3. PowerBI 增加数据源。
   进入PowerBI后，访问 Get data -> more -> 搜索 ClickHouse, 就可以看到 ClickHouse(Beta)(Custom) 的数据源了。

   

4. 填写 ByteHouse 的相关连接信息。

   1. Server：配置为 ByteHouse 的网络域名，您可以在 ByteHouse 控制台的 租户管理>基本信息>网络信息 中查看对应信息。详情请参见步骤二：配置网络信息。
   2. Port: 默认为8123。
   3. Database ：填写连接的数据库名称。
   4. Options：填写 SSLMode=require。
   5. 勾选 DirectQuery。
   

5. 填写 Bytehouse 的认证信息。

   

   参数	配置说明
   UserName	配置为 bytehouse。
   Password	配置为 <API_Key>，可以在 ByteHouse 控制台>租户管理>连接信息中获取的 API Key，详情请参考获取 API Key。

6. 连接成功后，加载对应的数据库表。

   

测试数据集 Demo #

DDL #

CREATE database IF NOT EXISTS hamster_odbc;
DROP table IF EXISTS hamster_odbc.all_types;
CREATE table IF NOT EXISTS hamster_odbc.all_types (
    c1 Int8,
    c2 Int16,
    c3 Int32,
    c4 Int64,
    c5 Float32,
    c6 Float64,
    c7 String,
    c8 Nullable(String),
    c9 Date,
    c10 DateTime,
    c11 UUID,
    c12 Array(Int8),
    c13 Enum8('v1'=1, 'v2'=2),
    c14 Enum16('v3'=1, 'v4'=2),
    c15 Decimal(2, 2),
    c16 FixedString(2),
    c17 Tuple(s String, i Int64),
    c18 Map(String, String)
) ENGINE = CnchMergeTree ORDER BY c1;

INSERT INTO hamster_odbc.all_types
(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,
--c17,
c18)
VALUES (
    127,
    32767,
    2147483647,
    9223372036854775807,
    100000.1,
    14000000000.1,
    'OK',
    NULL,
    '2022-12-30',
    '2022-12-30 13:44:17',
    417ddc5d-e556-4d27-95dd-a34d84e46a50,
    [1,2,3,4],
    'v1',
    'v3',
    4.20,
    'b\0',
--    ('a', 1),
    {'key1': 'value1', 'key2': 'value2'}
), (
    100,
    3200,
    2147483647,
    9223372036854775807,
    100000.1,
    14000000000.1,
    'OK',
    NULL,
    '2022-12-31',
    '2022-12-30 13:44:17',
    417ddc5d-e556-4d27-95dd-a34d84e46a50,
    [1,2,3,4],
    'v1',
    'v3',
    4.20,
    'b\0',
--    ('a', 1),
    {'key1': 'value1', 'key2': 'value2'}
), (
    100,
    3200,
    2147483647,
    9223372036854775807,
    100000.1,
    14000000000.1,
    'OK',
    NULL,
    '2022-12-29',
    '2022-12-30 13:44:17',
    417ddc5d-e556-4d27-95dd-a34d84e46a50,
    [1,2,3,4],
    'v1',
    'v3',
    4.20,
    'b\0',
--    ('a', 1),
    {'key1': 'value1', 'key2': 'value2'}
);
DROP TABLE IF EXISTS hamster_odbc.range_key_dictionary_source_table;
CREATE TABLE IF NOT EXISTS hamster_odbc.range_key_dictionary_source_table ( `key` UInt64, `value` String, `value_nullable` Nullable(String) ) ENGINE=CnchMergeTree() PRIMARY KEY tuple() ORDER BY tuple();
INSERT INTO hamster_odbc.`range_key_dictionary_source_table` VALUES (1, 'OK', NULL), (2, 'OK', 'OK');
DROP DICTIONARY IF EXISTS hamster_odbc.range_key_dictionary;
CREATE DICTIONARY hamster_odbc.range_key_dictionary (
  `key` UInt64,
  `value` String,
  `value_nullable` Nullable(String)
) PRIMARY KEY `key` SOURCE(
  CLICKHOUSE(USER 'default' TABLE 'range_key_dictionary_source_table' PASSWORD '' DB 'hamster_odbc')
) LIFETIME(MIN 10 MAX 20) LAYOUT(FLAT());

DQL #

通过 ODBC 添加数据时写入 SQL 语句中。

select 
ifNull(c7, 'was_null'),
ifNull(c8, 'OK'),
JSONExtractString('{"a": "OK", "b": [-100, 200.0, 300]}', 'a'),
multiIf(c7='OK', c7, 'a'='a', 'issue', 'issue'),
multiIf('a'='c', 'issue', c8 IS NULL, 'OK', 'issue'),
multiIf('a'='c', 'issue', c8='OK', 'issue', 'OK'),
if(c10=toDateTime('2022-12-30 13:44:17'), 'OK', 'issue'),
if(c10=toDateTime(1672407857, 'UTC'), 'OK', 'issue'),
if(date_trunc('hour', c10)=toDateTime('2022-12-30 13:00:00'), 'OK', 'issue'),
if(date_trunc('month', c10)=toDateTime('2022-12-01 00:00:00'), 'OK', 'issue'),
if(year(c10)=2022, 'OK', 'issue'),
if(month(c10)=12, 'OK', 'issue'),
if(date(c10)=toDateTime('2022-12-30 00:00:00'), 'OK', 'issue'),
case when 'a'='a' then 'OK' else 'issue' end,
case when 'a'='b' then 'issue' else 'OK' end,
if((SELECT argMin(c2, c1) FROM hamster_odbc.all_types)=3200, 'OK', 'issue'),
if((select uniqExactIf(number, number % 2 = 0) from numbers(100))=50, 'OK', 'issue'),
if((select max(c1) from hamster_odbc.all_types)=127, 'OK', 'issue'),
if(leftPad(c7, 5)='   OK', 'OK', 'issue'),
dictGetOrDefault('hamster_odbc.range_key_dictionary', 'value', toUInt64(1), 'issue') as dict_1,
dictGetOrDefault('hamster_odbc.range_key_dictionary', 'value', toUInt64(3), 'OK') as dict_2
from hamster_odbc.all_types limit 1;

select 
c1, c9,
leadInFrame(c9) OVER (PARTITION BY c1 ORDER BY c9 ASC ROWS
                 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
from hamster_odbc.all_types;

DQL 的输出如下：