apache doris demo

doris_test.livemd

Untitled notebook

Mix.install([
  {:kino_db, "~> 0.1.2"},
  {:myxql, github: "watsy0007/myxql", ref: "4510f6c", override: true}
])

Section

opts = [
  hostname: "127.0.0.1",
  port: 9030,
  username: "root",
  password: "",
  database: "demo"
]

{:ok, conn} = Kino.start_child({MyXQL, opts})

defmodule Doris do
  def query!(conn, sql) do
    MyXQL.query!(
      conn,
      sql,
      [],
      query_type: :text
    )
  end
end

Doris.query!(conn, "select city, sum(cost) as total_cost from expamle_tbl group by city")

doris 数据表设计

数据模型设计

aggregate 模型

Doris.query!(
  conn,
  """
  CREATE TABLE IF NOT EXISTS demo.expamle_tbl
  (
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
  )
  AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
  DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
  PROPERTIES (
  "replication_allocation" = "tag.location.default: 1"
  );
  """
)

Doris.query!(conn, "select * from expamle_tbl;")