Skip to content

Latest commit

 

History

History
577 lines (462 loc) · 14.4 KB

README-zh.md

File metadata and controls

577 lines (462 loc) · 14.4 KB
Raw

Coverage Status

clickhouse-orm (English README)

Join the chat at https://gitter.im/zimv/node-clickhouse-orm

Clickhouse ORM for Nodejs. 使用 HTTP 接口通信. 使用 TimonKK/clickhouse 作为客户端.

安装:

npm i clickhouse-orm

使用

创建ORM实例：

const { ClickhouseOrm } = require("clickhouse-orm");

const chOrm = ClickhouseOrm({
  db: {
    name: "orm_test",
  },
  debug: true,
  client: {
    url: "localhost",
    port: "8123",
    basicAuth: {
      username: "default",
      password: "",
    },
    debug: false,
    isUseGzip: true,
    format: "json", // "json" || "csv" || "tsv"
  },
});

定义数据模型：

import { DATA_TYPE, ModelSyncTableConfig } from 'clickhouse-orm';
const table1Schema: ModelSyncTableConfig<{
  status?: number;
  time: Date;
  browser?: string;
  browser_v?: string;
}> = {
  tableName: "table1",
  schema: {
    time: { type: DATA_TYPE.DateTime, default: Date },
    status: { type: DATA_TYPE.Int32 },
    browser: { type: DATA_TYPE.String },
    browser_v: { type: DATA_TYPE.String },
  },
  options: `ENGINE = MergeTree
  PARTITION BY toYYYYMM(time)
  ORDER BY time`,
  autoCreate: true,
  autoSync: true,
};

创建数据和查询：

// create database 'orm_test'
await chOrm.createDatabase();
// register schema and create [if] table
const Table1Model = await chOrm.model(table1Schema);

// create data
const resCreate = await Table1Model.create({
  status: 1,
  time: new Date(),
  browser: "chrome",
  browser_v: "90.0.1.21",
});
console.log("create:", resCreate);

// find
Table1Model.find({
  select: "*",
  limit: 3,
}).then((res) => {
  // SQL: SELECT * from orm_test.table1 LIMIT 3
  console.log("find:", res);
});

详情参考 Basic Example.

文档

注意: '?' 代表可选项

ClickhouseOrm

db : object<{name:string, engine?:string, cluster?:string}>

name: 数据库名称

engine?: 数据库引擎

cluster?: 集群名称

debug : boolean

Default: false

client : object

客户端驱动配置. 详情请看 TimonKK/clickhouse.

Model模型参数配置

1. ModelConfig

是否必选项 类型 描述
tableName true string 表名
schema true { [column]: { type, default? } } type定义数据类型, default 设置默认值 
import { DATA_TYPE, ModelConfig } from 'clickhouse-orm';
const xxxSchema: ModelConfig<{
  status?: number;
  time: Date;
  browser?: string;
  browser_v?: string;
}> = {
  // table name
  tableName: "xxx",
  // define column name
  schema: {
    time: { type: DATA_TYPE.DateTime, default: Date },
    status: { type: DATA_TYPE.Int32 },
    browser: { type: DATA_TYPE.String },
    browser_v: { type: DATA_TYPE.String },
  },
};

2. ModelSyncTableConfig （推荐）

是否必选项 类型 描述
tableName true string 表名
schema true { [column]: { type, default? } } type定义数据类型, default 设置默认值
options true string 建表的其他配置
autoCreate true boolean 是否自动建表
autoSync false boolean 是否自动同步表结构（谨慎使用）

支持自动创建表和自动同步表字段结构

import { DATA_TYPE, ModelSyncTableConfig } from 'clickhouse-orm';
const oldSchema: ModelSyncTableConfig<{
    time: Date;
    will_typeChanged?: number;
    will_deleted?: string;
  }> = {
  tableName: "xxx",
  schema: {
    time: { type: DATA_TYPE.DateTime, default: Date },
    will_typeChanged: { type: DATA_TYPE.Int16 },
    will_deleted: { type: DATA_TYPE.String },
  },
  options: `ENGINE = MergeTree
  PARTITION BY toYYYYMM(time)
  ORDER BY time`,
  autoCreate: true,
  autoSync: true,
};

模型创建时同步表结构(仅仅是字段列) 。模型创建时将会拉取远程数据库表的字段结构和当前代码中的 schema 进行对比，最终 ORM 会自动执行（增删改）语句。

const newSchema = {
  ...oldSchema,
  schema: {
    time: { type: DATA_TYPE.DateTime, default: Date },
    will_typeChanged: { type: DATA_TYPE.Int32 },
    add_column: { type: DATA_TYPE.String },
  }
}
chOrm.model(newSchema)

clickhouse-orm-log: sync table structure: ALTER TABLE orm_test.xxx DROP COLUMN will_deleted

clickhouse-orm-log: sync table structure: ALTER TABLE orm_test.xxx ADD COLUMN add_column String

clickhouse-orm-log: sync table structure: ALTER TABLE orm_test.xxx MODIFY COLUMN will_typeChanged Int32

无法识别字段名修改！下面的配置将会删除 column1，新增 column2。

oldSchema = {
  column1: { type: DATA_TYPE.String },
}
newSchema = {
  column2: { type: DATA_TYPE.String },
}

详情参考 SyncTable Example.

3. ModelSqlCreateTableConfig

是否必选项 类型 描述
tableName true string 表名
schema true { [column]: { type, default? } } type定义数据类型, default 设置默认值
createTable true string 自动建表的 SQL 语句，模型创建时会执行. 建议使用 'IF NOT EXISTS' 避免报错.
注意 !!! >>>>> 如果表结构要变动，此配置不会同步，你需要使用其他客户端（比如终端连接数据库）去执行改表语句。最后再回来修改代码

自定义建表语句，模型创建会自动创建表

import { DATA_TYPE, ModelSqlCreateTableConfig } from 'clickhouse-orm';
const xxxSchema: ModelSqlCreateTableConfig<{
    status?: number;
    time: Date;
    browser?: string;
    browser_v?: string;
  }> = {
  // table name
  tableName: "xxx",
  // define column name
  schema: {
    time: { type: DATA_TYPE.DateTime, default: Date },
    status: { type: DATA_TYPE.Int32 },
    browser: { type: DATA_TYPE.LowCardinality(DATA_TYPE.String) },
    browser_v: { type: DATA_TYPE.String  },
  },
  // create table sql
  createTable: (dbTableName) => {
    // dbTableName = db + '.' + tableName = (orm_test.table1)
    return `
      CREATE TABLE IF NOT EXISTS ${dbTableName}
      (
        time DateTime,
        status Int32,
        browser LowCardinality(String),
        browser_v String
      )
      ENGINE = MergeTree
      PARTITION BY toYYYYMM(time)
      ORDER BY time`;
  },
};

数据类型

ClickHouse 数据类型. 下面大部分数据 ORM 会验证，但也仅验证基本的数据类型number | string | boolean | date，不会用最标准的数据类型验证。比如 Int8，ORM 只验证 number 类型。

  UInt8;
  UInt16;
  UInt32;
  UInt64;
  Int8;
  Int16;
  Int32;
  Int64;
  Float32;
  Float64;
  Boolean;
  String;
  UUID;
  Date;
  Date32;
  DateTime;
  DateTime64;
  /**
   *
   * @param Number
   * @example DATA_TYPE.FixedString(3)
   */
  FixedString;
  /**
   *
   * @param DATA_TYPE
   * @example DATA_TYPE.LowCardinality(DATA_TYPE.String)
   */
  LowCardinality;
  /**
   *
   * @param string
   * @example DATA_TYPE.Enum8(`'hello' = 1, 'world' = 2`)
   * @desc number [-128, 127]
   */
  Enum8;
  /**
   *
   * @param string
   * @example DATA_TYPE.Enum16(`'hello' = 3000, 'world' = 3500`)
   * @desc number [-32768, 32767]
   */
  Enum16;
  /**
   *
   * @param columnType
   * Clickhouse dataTypes: Array(T), JSON, Map(key, value), IPv4, Nullable(), more...
   * @example DATA_TYPE.Other('Array(String)') , DATA_TYPE.Other('Int8')
   * @desc No `INSERT` data validation provided
   */
  Other;

详情参考 Datatype Example.

Log

setLogService 是一个全局配置，每一个 clickhouse-orm 实例都会被影响。

默认: console.log

使用其他日志库示例: winston

const { setLogService } = require("clickhouse-orm");
const winston = require("winston");
const logger = winston.createLogger();

setLogService(logger.info);

直接写纯 SQL 语句：

chOrm.client
  .query(`select * from orm_test.table1 limit 3`)
  .toPromise()
  .then((res) => {
    console.log("Use sql:", res);
  });

chOrm.client 是 TimonKK/clickhouse 的实例.

更多示例

查询

import * as dayjs from "dayjs";

queryExample1({
  Model: Table1Model,
  status: 1,
  beginTime: dayjs().subtract(1, "day").format("YYYY-MM-DD HH:mm:ss"),
  endTime: dayjs().format("YYYY-MM-DD HH:mm:ss"),
}).then((res) => {
  console.log("queryExample1:", res);
});

const queryExample1 = ({ Model, status, beginTime, endTime }) => {
  let wheres = [],
    where;
  if (status) wheres.push(`status='${status}'`);
  if (beginTime) wheres.push(`time>='${beginTime}'`);
  if (endTime) wheres.push(`time<='${endTime}'`);
  if (wheres.length > 0) where = wheres.join(" and ");

  return Model.find({
    where,
    select: `*`,
    orderBy: "time ASC",
    limit: 5,
  });
};

ORM 最终执行的 SQL:

SELECT * from orm_test.table1 where status='1' and time>='2022-02-04 15:34:22' and time<='2022-02-05 15:34:22'  ORDER BY time ASC LIMIT 5

统计

countExample1({
  Model: Table1Model,
}).then((res) => {
  console.log("countExample1:", res);
});

const countExample1 = ({ Model }) => {
  return Model.find({
    select: `count(*) AS total`,
  });
};

ORM 最终执行的 SQL:

SELECT count(*) AS total from orm_test.table1

聚合查询

Table1Model.find({
  select: `status,browser`,
  groupBy: "status,browser",
});

ORM 最终执行的 SQL:

SELECT status,browser from orm_test.table1  GROUP BY status,browser

嵌套查询

Table1Model.find([
  {
    select: `browser`,
    groupBy: "browser",
  },
  {
    select: `count() as browserTotal`,
  },
]);

ORM 最终执行的 SQL:

SELECT count() as browserTotal from (SELECT browser from orm_test.table1  GROUP BY browser  )

创建数据和保存

// new data model
const data = Table1Model.build();

// set value
data.time = new _Date_();
data.status = 1;
data.browser = "chrome";
data.browser_v = "90.0.1.21";

// do save
data.save().then((res) => {
  console.log("save:", res);
});

ORM 最终执行的 SQL:

INSERT INTO orm_test.table1 (time,status,browser,browser_v) [{"time":"2022-02-05T07:51:16.919Z","status":1,"browser":"chrome","browser_v":"90.0.1.21"}]\

创建并保存数据

//do create
await Table1Model.create({
  status: 1,
  time: new Date(),
  browser: "chrome",
  browser_v: "90.0.1.21",
});

ORM 最终执行的 SQL:

INSERT INTO orm_test.table1 (time,status,browser,browser_v) [{"time":"2022-02-05T07:51:16.919Z","status":1,"browser":"chrome","browser_v":"90.0.1.21"}]\

多条数据存储

const list = [
  { status: 2, browser: "IE", browser_v: "10.0.1.21" },
  { status: 2, browser: "FF", browser_v: "2.0.3" },
  { status: 3, browser: "IE", browser_v: "1.1.1" },
];

Table1Model.insertMany(list);
// or
Table1Model.insertMany(
  list.map((item) => {
    const data = Table1Model.build();
    // set value
    data.time = new Date();
    data.status = item.status;
    data.browser = item.browser;
    data.browser_v = item.browser_v;
    return data;
  })
);

ORM 最终执行的 SQL:

INSERT INTO orm_test.table1 (time,status,browser,browser_v) [{"time":"2022-02-05T07:34:22.226Z","status":2,"browser":"IE","browser_v":"10.0.1.21"},{"time":"2022-02-05T07:34:22.226Z","status":2,"browser":"FF","browser_v":"2.0.3"},{"time":"2022-02-05T07:34:22.226Z","status":3,"browser":"IE","browser_v":"1.1.1"}]

删除

Table1Model.delete({
  where: `browser='Chrome'`,
})

ORM 最终执行的 SQL:

ALTER TABLE orm_test.table1  DELETE  WHERE browser='Chrome'

Cluster 集群

创建一个集群实例：

const { ClickhouseOrm, DATA_TYPE, setLogService } = require("clickhouse-orm");

const chOrm = ClickhouseOrm({
  db: {
    name: "orm_cluster_test",
    cluster: "default_cluster",
  },
  // ...other
});
const table2Schema = {
  // table name
  tableName: "table2",
  ...other,
};

// create database 'orm_cluster_test'
// SQL: CREATE DATABASE IF NOT EXISTS orm_cluster_test ON CLUSTER default_cluster
await chOrm.createDatabase();

// register schema and create [if] table
// createTable() SQL: CREATE TABLE IF NOT EXISTS orm_cluster_test.table2 ON CLUSTER default_cluster...
const Table2Model = await chOrm.model(table2Schema);

微信讨论群

点击获取入群二维码

或者

Join the chat at https://gitter.im/zimv/node-clickhouse-orm