ClickHouse SQL syntax basic minimalist tutorial + usage example of bitmap data type
View all databases
SELECT * FROM system.databases;
Create database
create database circle_db engine=Ordinary comment 'circle_db';
Create table: partition by, primary key index order by
create table circle_db.bitmap_circle
(
row_key String default -1,
field_value_string String default -1,
field_value_long Int64 default -1,
field_value_double Float64 default -1,
object_id_map AggregateFunction(groupBitmap, UInt64),
p_date DateTime default now(),
table_code String default -1,
field_code String default -1
)
engine = AggregatingMergeTree()
partition by (p_date, table_code, field_code)
order by (row_key, field_value_string, field_value_long, field_value_double)
SETTINGS index_granularity = 128;
-- Since the number of rows of labels will not be too large, adjust the index granularity to an appropriate size (default 8192)Create base_user table structure
create table if not exists circle_db.base_user
(
id UInt64,
name String,
country String,
city String,
tag UInt32,
create_time DateTime
) engine = MergeTree
partition by toYYYYMM(create_time)
order by id
;Insert user sample data
insert into circle_db.base_user
values (1, 'Name1', 'China', 'Chongqing', 10001, now()),
(2, 'Name2', 'China', 'Chongqing', 10002, now()),
(3, 'Name3', 'China', 'Chongqing', 10003, now()),
(4, 'Name4', 'China', 'Chongqing', 10004, now()),
(5, 'Name5', 'China', 'Chongqing', 10005, now()),
(6, 'Name6', 'China', 'Chongqing', 10006, now()),
(7, 'Name7', 'China', 'Chongqing', 10007, now()),
(8, 'Name8', 'China', 'Chongqing', 10008, now()),
(9, 'Name9', 'China', 'Chongqing', 10009, now()),
(10, 'Name10', 'China', 'Chongqing', 10010, now()),
(11, 'Name11', 'China', 'Chongqing', 10011, now()),
(12, 'Name12', 'China', 'Chongqing', 10012, now()),
(13, 'Name13', 'China', 'Chongqing', 10013, now()),
(14, 'Name14', 'China', 'Chongqing', 10014, now()),
(15, 'Name15', 'China', 'Chongqing', 10015, now()),
(16, 'Name16', 'China', 'Chongqing', 10016, now()),
(17, 'Name17', 'China', 'Chongqing', 10017, now()),
(18, 'Name18', 'China', 'Chongqing', 10018, now()),
(19, 'Name19', 'China', 'Chongqing', 10019, now()),
(20, 'Name20', 'China', 'Chongqing', 10020, now()),
(21, 'Name21', 'China', 'Chongqing', 10021, now()),
(22, 'Name22', 'China', 'Chongqing', 10022, now()),
(23, 'Name23', 'China', 'Chongqing', 10023, now()),
(24, 'Name24', 'China', 'Chongqing', 10024, now()),
(25, 'Name25', 'China', 'Chongqing', 10025, now()),
(26, 'Name26', 'China', 'Chongqing', 10026, now()),
(27, 'Name27', 'China', 'Chongqing', 10027, now()),
(28, 'Name28', 'China', 'Chongqing', 10028, now()),
(29, 'Name29', 'China', 'Chongqing', 10029, now()),
(30, 'Name30', 'China', 'Chongqing', 10030, now()),
(31, 'Name31', 'China', 'Chongqing', 10031, now()),
(32, 'Name32', 'China', 'Chongqing', 10032, now()),
(33, 'Name33', 'China', 'Chongqing', 10033, now()),
(34, 'Name34', 'China', 'Chongqing', 10034, now()),
(35, 'Name35', 'China', 'Chongqing', 10035, now()),
(36, 'Name36', 'China', 'Chongqing', 10036, now()),
(37, 'Name37', 'China', 'Chongqing', 10037, now()),
(38, 'Name38', 'China', 'Chongqing', 10038, now()),
(39, 'Name39', 'China', 'Chongqing', 10039, now()),
(40, 'Name40', 'China', 'Chongqing', 10040, now()),
(41, 'Name41', 'China', 'Chongqing', 10041, now()),
(42, 'Name42', 'China', 'Chongqing', 10042, now()),
(43, 'Name43', 'China', 'Chongqing', 10043, now()),
(44, 'Name44', 'China', 'Chongqing', 10044, now()),
(45, 'Name45', 'China', 'Chongqing', 10045, now()),
(46, 'Name46', 'China', 'Chongqing', 10046, now()),
(47, 'Name47', 'China', 'Chongqing', 10047, now()),
(48, 'Name48', 'China', 'Chongqing', 10048, now()),
(49, 'Name49', 'China', 'Chongqing', 10049, now()),
(50, 'Name50', 'China', 'Chongqing', 10050, now()),
(51, 'Name51', 'China', 'Chongqing', 10051, now()),
(52, 'Name52', 'China', 'Chongqing', 10052, now()),
(53, 'Name53', 'China', 'Chongqing', 10053, now()),
(54, 'Name54', 'China', 'Chongqing', 10054, now()),
(55, 'Name55', 'China', 'Chongqing', 10055, now()),
(56, 'Name56', 'China', 'Chongqing', 10056, now()),
(57, 'Name57', 'China', 'Chongqing', 10057, now()),
(58, 'Name58', 'China', 'Chongqing', 10058, now()),
(59, 'Name59', 'China', 'Chongqing', 10059, now()),
(60, 'Name60', 'China', 'Chongqing', 10060, now()),
(61, 'Name61', 'China', 'Chongqing', 10061, now()),
(62, 'Name62', 'China', 'Chongqing', 10062, now()),
(63, 'Name63', 'China', 'Chongqing', 10063, now()),
(64, 'Name64', 'China', 'Chongqing', 10064, now()),
(65, 'Name65', 'China', 'Chongqing', 10065, now()),
(66, 'Name66', 'China', 'Chongqing', 10066, now()),
(67, 'Name67', 'China', 'Chongqing', 10067, now()),
(68, 'Name68', 'China', 'Chongqing', 10068, now()),
(69, 'Name69', 'China', 'Chongqing', 10069, now()),
(70, 'Name70', 'China', 'Chongqing', 10070, now()),
(71, 'Name71', 'China', 'Chongqing', 10071, now()),
(72, 'Name72', 'China', 'Chongqing', 10072, now()),
(73, 'Name73', 'China', 'Chongqing', 10073, now()),
(74, 'Name74', 'China', 'Chongqing', 10074, now()),
(75, 'Name75', 'China', 'Chongqing', 10075, now()),
(76, 'Name76', 'China', 'Chongqing', 10076, now()),
(77, 'Name77', 'China', 'Chongqing', 10077, now()),
(78, 'Name78', 'China', 'Chongqing', 10078, now()),
(79, 'Name79', 'China', 'Chongqing', 10079, now()),
(80, 'Name80', 'China', 'Chongqing', 10080, now()),
(81, 'Name81', 'China', 'Chongqing', 10081, now()),
(82, 'Name82', 'China', 'Chongqing', 10082, now()),
(83, 'Name83', 'China', 'Chongqing', 10083, now()),
(84, 'Name84', 'China', 'Chongqing', 10084, now()),
(85, 'Name85', 'China', 'Chongqing', 10085, now()),
(86, 'Name86', 'China', 'Chongqing', 10086, now()),
(87, 'Name87', 'China', 'Chongqing', 10087, now()),
(88, 'Name88', 'China', 'Chongqing', 10088, now()),
(89, 'Name89', 'China', 'Chongqing', 10089, now()),
(90, 'Name90', 'China', 'Chongqing', 10090, now()),
(91, 'Name91', 'China', 'Chongqing', 10091, now()),
(92, 'Name92', 'China', 'Chongqing', 10092, now()),
(93, 'Name93', 'China', 'Chongqing', 10093, now()),
(94, 'Name94', 'China', 'Chongqing', 10094, now()),
(95, 'Name95', 'China', 'Chongqing', 10095, now()),
(96, 'Name96', 'China', 'Chongqing', 10096, now()),
(97, 'Name97', 'China', 'Chongqing', 10097, now()),
(98, 'Name98', 'China', 'Chongqing', 10098, now()),
(99, 'Name99', 'China', 'Chongqing', 10099, now()),
(100, 'Name100', 'China', 'Chongqing', 10100, now()),
(101, 'Name101', 'China', 'Chongqing', 10101, now()),
(102, 'Name102', 'China', 'Chongqing', 10102, now()),
(103, 'Name103', 'China', 'Chongqing', 10103, now()),
(104, 'Name104', 'China', 'Chongqing', 10104, now()),
(105, 'Name105', 'China', 'Chongqing', 10105, now()),
(106, 'Name106', 'China', 'Chongqing', 10106, now()),
(107, 'Name107', 'China', 'Chongqing', 10107, now()),
(108, 'Name108', 'China', 'Chongqing', 10108, now()),
(109, 'Name109', 'China', 'Chongqing', 10109, now()),
(110, 'Name110', 'China', 'Chongqing', 10110, now()),
(111, 'Name111', 'China', 'Chongqing', 10111, now()),
(112, 'Name112', 'China', 'Chongqing', 10112, now()),
(113, 'Name113', 'China', 'Chongqing', 10113, now()),
(114, 'Name114', 'China', 'Chongqing', 10114, now()),
(115, 'Name115', 'China', 'Chongqing', 10115, now()),
(116, 'Name116', 'China', 'Chongqing', 10116, now()),
(117, 'Name117', 'China', 'Chongqing', 10117, now()),
(118, 'Name118', 'China', 'Chongqing', 10118, now()),
(119, 'Name119', 'China', 'Chongqing', 10119, now()),
(120, 'Name120', 'China', 'Chongqing', 10120, now()),
(121, 'Name121', 'China', 'Chongqing', 10121, now()),
(122, 'Name122', 'China', 'Chongqing', 10122, now()),
(123, 'Name123', 'China', 'Chongqing', 10123, now()),
(124, 'Name124', 'China', 'Chongqing', 10124, now()),
(125, 'Name125', 'China', 'Chongqing', 10125, now()),
(126, 'Name126', 'China', 'Chongqing', 10126, now()),
(127, 'Name127', 'China', 'Chongqing', 10127, now()),
(128, 'Name128', 'China', 'Chongqing', 10128, now()),
(129, 'Name129', 'China', 'Chongqing', 10129, now()),
(130, 'Name130', 'China', 'Chongqing', 10130, now()),
(131, 'Name131', 'China', 'Chongqing', 10131, now()),
(132, 'Name132', 'China', 'Chongqing', 10132, now()),
(133, 'Name133', 'China', 'Chongqing', 10133, now()),
(134, 'Name134', 'China', 'Chongqing', 10134, now()),
(135, 'Name135', 'China', 'Chongqing', 10135, now()),
(136, 'Name136', 'China', 'Chongqing', 10136, now()),
(137, 'Name137', 'China', 'Chongqing', 10137, now()),
(138, 'Name138', 'China', 'Chongqing', 10138, now()),
(139, 'Name139', 'China', 'Chongqing', 10139, now()),
(140, 'Name140', 'China', 'Chongqing', 10140, now()),
(141, 'Name141', 'China', 'Chongqing', 10141, now()),
(142, 'Name142', 'China', 'Chongqing', 10142, now()),
(143, 'Name143', 'China', 'Chongqing', 10143, now()),
(144, 'Name144', 'China', 'Chongqing', 10144, now()),
(145, 'Name145', 'China', 'Chongqing', 10145, now()),
(146, 'Name146', 'China', 'Chongqing', 10146, now()),
(147, 'Name147', 'China', 'Chongqing', 10147, now()),
(148, 'Name148', 'China', 'Chongqing', 10148, now()),
(149, 'Name149', 'China', 'Chongqing', 10149, now()),
(150, 'Name150', 'China', 'Chongqing', 10150, now()),
(151, 'Name151', 'China', 'Chongqing', 10151, now()),
(152, 'Name152', 'China', 'Chongqing', 10152, now()),
(153, 'Name153', 'China', 'Chongqing', 10153, now()),
(154, 'Name154', 'China', 'Chongqing', 10154, now()),
(155, 'Name155', 'China', 'Chongqing', 10155, now()),
(156, 'Name156', 'China', 'Chongqing', 10156, now()),
(157, 'Name157', 'China', 'Chongqing', 10157, now()),
(158, 'Name158', 'China', 'Chongqing', 10158, now()),
(159, 'Name159', 'China', 'Chongqing', 10159, now()),
(160, 'Name160', 'China', 'Chongqing', 10160, now()),
(161, 'Name161', 'China', 'Chongqing', 10161, now()),
(162, 'Name162', 'China', 'Chongqing', 10162, now()),
(163, 'Name163', 'China', 'Chongqing', 10163, now()),
(164, 'Name164', 'China', 'Chongqing', 10164, now()),
(165, 'Name165', 'China', 'Chongqing', 10165, now()),
(166, 'Name166', 'China', 'Chongqing', 10166, now()),
(167, 'Name167', 'China', 'Chongqing', 10167, now()),
(168, 'Name168', 'China', 'Chongqing', 10168, now()),
(169, 'Name169', 'China', 'Chongqing', 10169, now()),
(170, 'Name170', 'China', 'Chongqing', 10170, now()),
(171, 'Name171', 'China', 'Chongqing', 10171, now()),
(172, 'Name172', 'China', 'Chongqing', 10172, now()),
(173, 'Name173', 'China', 'Chongqing', 10173, now()),
(174, 'Name174', 'China', 'Chongqing', 10174, now()),
(175, 'Name175', 'China', 'Chongqing', 10175, now()),
(176, 'Name176', 'China', 'Chongqing', 10176, now()),
(177, 'Name177', 'China', 'Chongqing', 10177, now()),
(178, 'Name178', 'China', 'Chongqing', 10178, now()),
(179, 'Name179', 'China', 'Chongqing', 10179, now()),
(180, 'Name180', 'China', 'Chongqing', 10180, now()),
(181, 'Name181', 'China', 'Chongqing', 10181, now()),
(182, 'Name182', 'China', 'Chongqing', 10182, now()),
(183, 'Name183', 'China', 'Chongqing', 10183, now()),
(184, 'Name184', 'China', 'Chongqing', 10184, now()),
(185, 'Name185', 'China', 'Chongqing', 10185, now()),
(186, 'Name186', 'China', 'Chongqing', 10186, now()),
(187, 'Name187', 'China', 'Chongqing', 10187, now()),
(188, 'Name188', 'China', 'Chongqing', 10188, now()),
(189, 'Name189', 'China', 'Chongqing', 10189, now()),
(190, 'Name190', 'China', 'Chongqing', 10190, now()),
(191, 'Name191', 'China', 'Chongqing', 10191, now()),
(192, 'Name192', 'China', 'Chongqing', 10192, now()),
(193, 'Name193', 'China', 'Chongqing', 10193, now()),
(194, 'Name194', 'China', 'Chongqing', 10194, now()),
(195, 'Name195', 'China', 'Chongqing', 10195, now()),
(196, 'Name196', 'China', 'Chongqing', 10196, now()),
(197, 'Name197', 'China', 'Chongqing', 10197, now()),
(198, 'Name198', 'China', 'Chongqing', 10198, now()),
(199, 'Name199', 'China', 'Chongqing', 10199, now()),
(200, 'Name200', 'China', 'Chongqing', 10200, now()),
(201, 'Name201', 'China', 'Chongqing', 10201, now()),
(202, 'Name202', 'China', 'Chongqing', 10202, now()),
(203, 'Name203', 'China', 'Chongqing', 10203, now()),
(204, 'Name204', 'China', 'Chongqing', 10204, now()),
(205, 'Name205', 'China', 'Chongqing', 10205, now()),
(206, 'Name206', 'China', 'Chongqing', 10206, now()),
(207, 'Name207', 'China', 'Chongqing', 10207, now()),
(208, 'Name208', 'China', 'Chongqing', 10208, now()),
(209, 'Name209', 'China', 'Chongqing', 10209, now()),
(210, 'Name210', 'China', 'Chongqing', 10210, now()),
(211, 'Name211', 'China', 'Chongqing', 10211, now()),
(212, 'Name212', 'China', 'Chongqing', 10212, now()),
(213, 'Name213', 'China', 'Chongqing', 10213, now()),
(214, 'Name214', 'China', 'Chongqing', 10214, now()),
(215, 'Name215', 'China', 'Chongqing', 10215, now()),
(216, 'Name216', 'China', 'Chongqing', 10216, now()),
(217, 'Name217', 'China', 'Chongqing', 10217, now()),
(218, 'Name218', 'China', 'Chongqing', 10218, now()),
(219, 'Name219', 'China', 'Chongqing', 10219, now()),
(220, 'Name220', 'China', 'Chongqing', 10220, now()),
(221, 'Name221', 'China', 'Chongqing', 10221, now()),
(222, 'Name222', 'China', 'Chongqing', 10222, now()),
(223, 'Name223', 'China', 'Chongqing', 10223, now()),
(224, 'Name224', 'China', 'Chongqing', 10224, now()),
(225, 'Name225', 'China', 'Chongqing', 10225, now()),
(226, 'Name226', 'China', 'Chongqing', 10226, now()),
(227, 'Name227', 'China', 'Chongqing', 10227, now()),
(228, 'Name228', 'China', 'Chongqing', 10228, now()),
(229, 'Name229', 'China', 'Chongqing', 10229, now()),
(230, 'Name230', 'China', 'Chongqing', 10230, now()),
(231, 'Name231', 'China', 'Chongqing', 10231, now()),
(232, 'Name232', 'China', 'Chongqing', 10232, now()),
(233, 'Name233', 'China', 'Chongqing', 10233, now()),
(234, 'Name234', 'China', 'Chongqing', 10234, now()),
(235, 'Name235', 'China', 'Chongqing', 10235, now()),
(236, 'Name236', 'China', 'Chongqing', 10236, now()),
(237, 'Name237', 'China', 'Chongqing', 10237, now()),
(238, 'Name238', 'China', 'Chongqing', 10238, now()),
(239, 'Name239', 'China', 'Chongqing', 10239, now()),
(240, 'Name240', 'China', 'Chongqing', 10240, now()),
(241, 'Name241', 'China', 'Chongqing', 10241, now()),
(242, 'Name242', 'China', 'Chongqing', 10242, now()),
(243, 'Name243', 'China', 'Chongqing', 10243, now()),
(244, 'Name244', 'China', 'Chongqing', 10244, now()),
(245, 'Name245', 'China', 'Chongqing', 10245, now()),
(246, 'Name246', 'China', 'Chongqing', 10246, now()),
(247, 'Name247', 'China', 'Chongqing', 10247, now()),
(248, 'Name248', 'China', 'Chongqing', 10248, now()),
(249, 'Name249', 'China', 'Chongqing', 10249, now()),
(250, 'Name250', 'China', 'Chongqing', 10250, now()),
(251, 'Name251', 'China', 'Chongqing', 10251, now()),
(252, 'Name252', 'China', 'Chongqing', 10252, now()),
(253, 'Name253', 'China', 'Chongqing', 10253, now()),
(254, 'Name254', 'China', 'Chongqing', 10254, now()),
(255, 'Name255', 'China', 'Chongqing', 10255, now()),
(256, 'Name256', 'China', 'Chongqing', 10256, now()),
(257, 'Name257', 'China', 'Chongqing', 10257, now()),
(258, 'Name258', 'China', 'Chongqing', 10258, now()),
(259, 'Name259', 'China', 'Chongqing', 10259, now()),
(260, 'Name260', 'China', 'Chongqing', 10260, now()),
(261, 'Name261', 'China', 'Chongqing', 10261, now()),
(262, 'Name262', 'China', 'Chongqing', 10262, now()),
(263, 'Name263', 'China', 'Chongqing', 10263, now()),
(264, 'Name264', 'China', 'Chongqing', 10264, now()),
(265, 'Name265', 'China', 'Chongqing', 10265, now()),
(266, 'Name266', 'China', 'Chongqing', 10266, now()),
(267, 'Name267', 'China', 'Chongqing', 10267, now()),
(268, 'Name268', 'China', 'Chongqing', 10268, now()),
(269, 'Name269', 'China', 'Chongqing', 10269, now()),
(270, 'Name270', 'China', 'Chongqing', 10270, now()),
(271, 'Name271', 'China', 'Chongqing', 10271, now()),
(272, 'Name272', 'China', 'Chongqing', 10272, now()),
(273, 'Name273', 'China', 'Chongqing', 10273, now()),
(274, 'Name274', 'China', 'Chongqing', 10274, now()),
(275, 'Name275', 'China', 'Chongqing', 10275, now()),
(276, 'Name276', 'China', 'Chongqing', 10276, now()),
(277, 'Name277', 'China', 'Chongqing', 10277, now()),
(278, 'Name278', 'China', 'Chongqing', 10278, now()),
(279, 'Name279', 'China', 'Chongqing', 10279, now()),
(280, 'Name280', 'China', 'Chongqing', 10280, now()),
(281, 'Name281', 'China', 'Chongqing', 10281, now()),
(282, 'Name282', 'China', 'Chongqing', 10282, now()),
(283, 'Name283', 'China', 'Chongqing', 10283, now()),
(284, 'Name284', 'China', 'Chongqing', 10284, now()),
(285, 'Name285', 'China', 'Chongqing', 10285, now()),
(286, 'Name286', 'China', 'Chongqing', 10286, now()),
(287, 'Name287', 'China', 'Chongqing', 10287, now()),
(288, 'Name288', 'China', 'Chongqing', 10288, now()),
(289, 'Name289', 'China', 'Chongqing', 10289, now()),
(290, 'Name290', 'China', 'Chongqing', 10290, now()),
(291, 'Name291', 'China', 'Chongqing', 10291, now()),
(292, 'Name292', 'China', 'Chongqing', 10292, now()),
(293, 'Name293', 'China', 'Chongqing', 10293, now()),
(294, 'Name294', 'China', 'Chongqing', 10294, now()),
(295, 'Name295', 'China', 'Chongqing', 10295, now()),
(296, 'Name296', 'China', 'Chongqing', 10296, now()),
(297, 'Name297', 'China', 'Chongqing', 10297, now()),
(298, 'Name298', 'China', 'Chongqing', 10298, now()),
(299, 'Name299', 'China', 'Chongqing', 10299, now()),
(300, 'Name300', 'China', 'Chongqing', 10300, now()),
(301, 'Name301', 'China', 'Chongqing', 10301, now()),
(302, 'Name302', 'China', 'Chongqing', 10302, now()),
(303, 'Name303', 'China', 'Chongqing', 10303, now()),
(304, 'Name304', 'China', 'Chongqing', 10304, now()),
(305, 'Name305', 'China', 'Chongqing', 10305, now()),
(306, 'Name306', 'China', 'Chongqing', 10306, now()),
(307, 'Name307', 'China', 'Chongqing', 10307, now()),
(308, 'Name308', 'China', 'Chongqing', 10308, now()),
(309, 'Name309', 'China', 'Chongqing', 10309, now()),
(310, 'Name310', 'China', 'Chongqing', 10310, now()),
(311, 'Name311', 'China', 'Chongqing', 10311, now()),
(312, 'Name312', 'China', 'Chongqing', 10312, now()),
(313, 'Name313', 'China', 'Chongqing', 10313, now()),
(314, 'Name314', 'China', 'Chongqing', 10314, now()),
(315, 'Name315', 'China', 'Chongqing', 10315, now()),
(316, 'Name316', 'China', 'Chongqing', 10316, now()),
(317, 'Name317', 'China', 'Chongqing', 10317, now()),
(318, 'Name318', 'China', 'Chongqing', 10318, now()),
(319, 'Name319', 'China', 'Chongqing', 10319, now()),
(320, 'Name320', 'China', 'Chongqing', 10320, now()),
(321, 'Name321', 'China', 'Chongqing', 10321, now()),
(322, 'Name322', 'China', 'Chongqing', 10322, now()),
(323, 'Name323', 'China', 'Chongqing', 10323, now()),
(324, 'Name324', 'China', 'Chongqing', 10324, now()),
(325, 'Name325', 'China', 'Chongqing', 10325, now()),
(326, 'Name326', 'China', 'Chongqing', 10326, now()),
(327, 'Name327', 'China', 'Chongqing', 10327, now()),
(328, 'Name328', 'China', 'Chongqing', 10328, now()),
(329, 'Name329', 'China', 'Chongqing', 10329, now()),
(330, 'Name330', 'China', 'Chongqing', 10330, now()),
(331, 'Name331', 'China', 'Chongqing', 10331, now()),
(332, 'Name332', 'China', 'Chongqing', 10332, now()),
(333, 'Name333', 'China', 'Chongqing', 10333, now()),
(334, 'Name334', 'China', 'Chongqing', 10334, now()),
(335, 'Name335', 'China', 'Chongqing', 10335, now()),
(336, 'Name336', 'China', 'Chongqing', 10336, now()),
(337, 'Name337', 'China', 'Chongqing', 10337, now()),
(338, 'Name338', 'China', 'Chongqing', 10338, now()),
(339, 'Name339', 'China', 'Chongqing', 10339, now()),
(340, 'Name340', 'China', 'Chongqing', 10340, now()),
(341, 'Name341', 'China', 'Chongqing', 10341, now()),
(342, 'Name342', 'China', 'Chongqing', 10342, now()),
(343, 'Name343', 'China', 'Chongqing', 10343, now()),
(344, 'Name344', 'China', 'Chongqing', 10344, now()),
(345, 'Name345', 'China', 'Chongqing', 10345, now()),
(346, 'Name346', 'China', 'Chongqing', 10346, now()),
(347, 'Name347', 'China', 'Chongqing', 10347, now()),
(348, 'Name348', 'China', 'Chongqing', 10348, now()),
(349, 'Name349', 'China', 'Chongqing', 10349, now()),
(350, 'Name350', 'China', 'Chongqing', 10350, now()),
(351, 'Name351', 'China', 'Chongqing', 10351, now()),
(352, 'Name352', 'China', 'Chongqing', 10352, now()),
(353, 'Name353', 'China', 'Chongqing', 10353, now()),
(354, 'Name354', 'China', 'Chongqing', 10354, now()),
(355, 'Name355', 'China', 'Chongqing', 10355, now()),
(356, 'Name356', 'China', 'Chongqing', 10356, now()),
(357, 'Name357', 'China', 'Chongqing', 10357, now()),
(358, 'Name358', 'China', 'Chongqing', 10358, now()),
(359, 'Name359', 'China', 'Chongqing', 10359, now()),
(360, 'Name360', 'China', 'Chongqing', 10360, now()),
(361, 'Name361', 'China', 'Chongqing', 10361, now()),
(362, 'Name362', 'China', 'Chongqing', 10362, now()),
(363, 'Name363', 'China', 'Chongqing', 10363, now()),
(364, 'Name364', 'China', 'Chongqing', 10364, now()),
(365, 'Name365', 'China', 'Chongqing', 10365, now()),
(366, 'Name366', 'China', 'Chongqing', 10366, now()),
(367, 'Name367', 'China', 'Chongqing', 10367, now()),
(368, 'Name368', 'China', 'Chongqing', 10368, now()),
(369, 'Name369', 'China', 'Chongqing', 10369, now()),
(370, 'Name370', 'China', 'Chongqing', 10370, now()),
(371, 'Name371', 'China', 'Chongqing', 10371, now()),
(372, 'Name372', 'China', 'Chongqing', 10372, now()),
(373, 'Name373', 'China', 'Chongqing', 10373, now()),
(374, 'Name374', 'China', 'Chongqing', 10374, now()),
(375, 'Name375', 'China', 'Chongqing', 10375, now()),
(376, 'Name376', 'China', 'Chongqing', 10376, now()),
(377, 'Name377', 'China', 'Chongqing', 10377, now()),
(378, 'Name378', 'China', 'Chongqing', 10378, now()),
(379, 'Name379', 'China', 'Chongqing', 10379, now()),
(380, 'Name380', 'China', 'Chongqing', 10380, now()),
(381, 'Name381', 'China', 'Chongqing', 10381, now()),
(382, 'Name382', 'China', 'Chongqing', 10382, now()),
(383, 'Name383', 'China', 'Chongqing', 10383, now()),
(384, 'Name384', 'China', 'Chongqing', 10384, now()),
(385, 'Name385', 'China', 'Chongqing', 10385, now()),
(386, 'Name386', 'China', 'Chongqing', 10386, now()),
(387, 'Name387', 'China', 'Chongqing', 10387, now()),
(388, 'Name388', 'China', 'Chongqing', 10388, now()),
(389, 'Name389', 'China', 'Chongqing', 10389, now()),
(390, 'Name390', 'China', 'Chongqing', 10390, now()),
(391, 'Name391', 'China', 'Chongqing', 10391, now()),
(392, 'Name392', 'China', 'Chongqing', 10392, now()),
(393, 'Name393', 'China', 'Chongqing', 10393, now()),
(394, 'Name394', 'China', 'Chongqing', 10394, now()),
(395, 'Name395', 'China', 'Chongqing', 10395, now()),
(396, 'Name396', 'China', 'Chongqing', 10396, now()),
(397, 'Name397', 'China', 'Chongqing', 10397, now()),
(398, 'Name398', 'China', 'Chongqing', 10398, now()),
(399, 'Name399', 'China', 'Chongqing', 10399, now()),
(400, 'Name400', 'China', 'Chongqing', 10400, now()),
(401, 'Name401', 'China', 'Chongqing', 10401, now()),
(402, 'Name402', 'China', 'Chongqing', 10402, now()),
(403, 'Name403', 'China', 'Chongqing', 10403, now()),
(404, 'Name404', 'China', 'Chongqing', 10404, now()),
(405, 'Name405', 'China', 'Chongqing', 10405, now()),
(406, 'Name406', 'China', 'Chongqing', 10406, now()),
(407, 'Name407', 'China', 'Chongqing', 10407, now()),
(408, 'Name408', 'China', 'Chongqing', 10408, now()),
(409, 'Name409', 'China', 'Chongqing', 10409, now()),
(410, 'Name410', 'China', 'Chongqing', 10410, now()),
(411, 'Name411', 'China', 'Chongqing', 10411, now()),
(412, 'Name412', 'China', 'Chongqing', 10412, now()),
(413, 'Name413', 'China', 'Chongqing', 10413, now()),
(414, 'Name414', 'China', 'Chongqing', 10414, now()),
(415, 'Name415', 'China', 'Chongqing', 10415, now()),
(416, 'Name416', 'China', 'Chongqing', 10416, now()),
(417, 'Name417', 'China', 'Chongqing', 10417, now()),
(418, 'Name418', 'China', 'Chongqing', 10418, now()),
(419, 'Name419', 'China', 'Chongqing', 10419, now()),
(420, 'Name420', 'China', 'Chongqing', 10420, now()),
(421, 'Name421', 'China', 'Chongqing', 10421, now()),
(422, 'Name422', 'China', 'Chongqing', 10422, now()),
(423, 'Name423', 'China', 'Chongqing', 10423, now()),
(424, 'Name424', 'China', 'Chongqing', 10424, now()),
(425, 'Name425', 'China', 'Chongqing', 10425, now()),
(426, 'Name426', 'China', 'Chongqing', 10426, now()),
(427, 'Name427', 'China', 'Chongqing', 10427, now()),
(428, 'Name428', 'China', 'Chongqing', 10428, now()),
(429, 'Name429', 'China', 'Chongqing', 10429, now()),
(430, 'Name430', 'China', 'Chongqing', 10430, now()),
(431, 'Name431', 'China', 'Chongqing', 10431, now()),
(432, 'Name432', 'China', 'Chongqing', 10432, now()),
(433, 'Name433', 'China', 'Chongqing', 10433, now()),
(434, 'Name434', 'China', 'Chongqing', 10434, now()),
(435, 'Name435', 'China', 'Chongqing', 10435, now()),
(436, 'Name436', 'China', 'Chongqing', 10436, now()),
(437, 'Name437', 'China', 'Chongqing', 10437, now()),
(438, 'Name438', 'China', 'Chongqing', 10438, now()),
(439, 'Name439', 'China', 'Chongqing', 10439, now()),
(440, 'Name440', 'China', 'Chongqing', 10440, now()),
(441, 'Name441', 'China', 'Chongqing', 10441, now()),
(442, 'Name442', 'China', 'Chongqing', 10442, now()),
(443, 'Name443', 'China', 'Chongqing', 10443, now()),
(444, 'Name444', 'China', 'Chongqing', 10444, now()),
(445, 'Name445', 'China', 'Chongqing', 10445, now()),
(446, 'Name446', 'China', 'Chongqing', 10446, now()),
(447, 'Name447', 'China', 'Chongqing', 10447, now()),
(448, 'Name448', 'China', 'Chongqing', 10448, now()),
(449, 'Name449', 'China', 'Chongqing', 10449, now()),
(450, 'Name450', 'China', 'Chongqing', 10450, now()),
(451, 'Name451', 'China', 'Chongqing', 10451, now()),
(452, 'Name452', 'China', 'Chongqing', 10452, now()),
(453, 'Name453', 'China', 'Chongqing', 10453, now()),
(454, 'Name454', 'China', 'Chongqing', 10454, now()),
(455, 'Name455', 'China', 'Chongqing', 10455, now()),
(456, 'Name456', 'China', 'Chongqing', 10456, now()),
(457, 'Name457', 'China', 'Chongqing', 10457, now()),
(458, 'Name458', 'China', 'Chongqing', 10458, now()),
(459, 'Name459', 'China', 'Chongqing', 10459, now()),
(460, 'Name460', 'China', 'Chongqing', 10460, now()),
(461, 'Name461', 'China', 'Chongqing', 10461, now()),
(462, 'Name462', 'China', 'Chongqing', 10462, now()),
(463, 'Name463', 'China', 'Chongqing', 10463, now()),
(464, 'Name464', 'China', 'Chongqing', 10464, now()),
(465, 'Name465', 'China', 'Chongqing', 10465, now()),
(466, 'Name466', 'China', 'Chongqing', 10466, now()),
(467, 'Name467', 'China', 'Chongqing', 10467, now()),
(468, 'Name468', 'China', 'Chongqing', 10468, now()),
(469, 'Name469', 'China', 'Chongqing', 10469, now()),
(470, 'Name470', 'China', 'Chongqing', 10470, now()),
(471, 'Name471', 'China', 'Chongqing', 10471, now()),
(472, 'Name472', 'China', 'Chongqing', 10472, now()),
(473, 'Name473', 'China', 'Chongqing', 10473, now()),
(474, 'Name474', 'China', 'Chongqing', 10474, now()),
(475, 'Name475', 'China', 'Chongqing', 10475, now()),
(476, 'Name476', 'China', 'Chongqing', 10476, now()),
(477, 'Name477', 'China', 'Chongqing', 10477, now()),
(478, 'Name478', 'China', 'Chongqing', 10478, now()),
(479, 'Name479', 'China', 'Chongqing', 10479, now()),
(480, 'Name480', 'China', 'Chongqing', 10480, now()),
(481, 'Name481', 'China', 'Chongqing', 10481, now()),
(482, 'Name482', 'China', 'Chongqing', 10482, now()),
(483, 'Name483', 'China', 'Chongqing', 10483, now()),
(484, 'Name484', 'China', 'Chongqing', 10484, now()),
(485, 'Name485', 'China', 'Chongqing', 10485, now()),
(486, 'Name486', 'China', 'Chongqing', 10486, now()),
(487, 'Name487', 'China', 'Chongqing', 10487, now()),
(488, 'Name488', 'China', 'Chongqing', 10488, now()),
(489, 'Name489', 'China', 'Chongqing', 10489, now()),
(490, 'Name490', 'China', 'Chongqing', 10490, now()),
(491, 'Name491', 'China', 'Chongqing', 10491, now()),
(492, 'Name492', 'China', 'Chongqing', 10492, now()),
(493, 'Name493', 'China', 'Chongqing', 10493, now()),
(494, 'Name494', 'China', 'Chongqing', 10494, now()),
(495, 'Name495', 'China', 'Chongqing', 10495, now()),
(496, 'Name496', 'China', 'Chongqing', 10496, now()),
(497, 'Name497', 'China', 'Chongqing', 10497, now()),
(498, 'Name498', 'China', 'Chongqing', 10498, now()),
(499, 'Name499', 'China', 'Chongqing', 10499, now()),
(500, 'Name500', 'China', 'Chongqing', 10500, now()),
(501, 'Name501', 'China', 'Chongqing', 10501, now()),
(502, 'Name502', 'China', 'Chongqing', 10502, now()),
(503, 'Name503', 'China', 'Chongqing', 10503, now()),
(504, 'Name504', 'China', 'Chongqing', 10504, now()),
(505, 'Name505', 'China', 'Chongqing', 10505, now()),
(506, 'Name506', 'China', 'Chongqing', 10506, now()),
(507, 'Name507', 'China', 'Chongqing', 10507, now()),
(508, 'Name508', 'China', 'Chongqing', 10508, now()),
(509, 'Name509', 'China', 'Chongqing', 10509, now()),
(510, 'Name510', 'China', 'Chongqing', 10510, now()),
(511, 'Name511', 'China', 'Chongqing', 10511, now()),
(512, 'Name512', 'China', 'Chongqing', 10512, now()),
(513, 'Name513', 'China', 'Chongqing', 10513, now()),
(514, 'Name514', 'China', 'Chongqing', 10514, now()),
(515, 'Name515', 'China', 'Chongqing', 10515, now()),
(516, 'Name516', 'China', 'Chongqing', 10516, now()),
(517, 'Name517', 'China', 'Chongqing', 10517, now()),
(518, 'Name518', 'China', 'Chongqing', 10518, now()),
(519, 'Name519', 'China', 'Chongqing', 10519, now()),
(520, 'Name520', 'China', 'Chongqing', 10520, now()),
(521, 'Name521', 'China', 'Chongqing', 10521, now()),
(522, 'Name522', 'China', 'Chongqing', 10522, now()),
(523, 'Name523', 'China', 'Chongqing', 10523, now()),
(524, 'Name524', 'China', 'Chongqing', 10524, now()),
(525, 'Name525', 'China', 'Chongqing', 10525, now()),
(526, 'Name526', 'China', 'Chongqing', 10526, now()),
(527, 'Name527', 'China', 'Chongqing', 10527, now()),
(528, 'Name528', 'China', 'Chongqing', 10528, now()),
(529, 'Name529', 'China', 'Chongqing', 10529, now()),
(530, 'Name530', 'China', 'Chongqing', 10530, now()),
(531, 'Name531', 'China', 'Chongqing', 10531, now()),
(532, 'Name532', 'China', 'Chongqing', 10532, now()),
(533, 'Name533', 'China', 'Chongqing', 10533, now()),
(534, 'Name534', 'China', 'Chongqing', 10534, now()),
(535, 'Name535', 'China', 'Chongqing', 10535, now()),
(536, 'Name536', 'China', 'Chongqing', 10536, now()),
(537, 'Name537', 'China', 'Chongqing', 10537, now()),
(538, 'Name538', 'China', 'Chongqing', 10538, now()),
(539, 'Name539', 'China', 'Chongqing', 10539, now()),
(540, 'Name540', 'China', 'Chongqing', 10540, now())
;
insert into circle_db.base_user
values (541, 'Name541', 'China', 'Chongqing', 10001, now());Import data: groupBitmapState(id) as object_id_map, constructed from the aggregation function groupBitmapState
AggregateFunction(groupBitmap, UInt64) Bitmap field value
insert into circle_db.bitmap_circle
select tag as row_key,
'-1' as field_value_string,
tag as field_value_long,
'-1' as field_value_double,
groupBitmapState(id) as object_id_map,
now() as p_date,
'user_basic' as table_code,
'city' as field_code
from circle_db.base_user
group by tag
;Query tag data: bitmapToArray(groupBitmapMergeState(object_id_map)
select row_key,
table_code,
field_code,
field_value_long,
bitmapToArray(groupBitmapMergeState(object_id_map))
from circle_db.bitmap_circle
group by row_key, table_code, field_code, field_value_long;Query table
select * from circle_db.bitmap_circle; select * from circle_db.base_user;
Common bitmap functions
1. Construct bitmap object with unsigned integer
select bitmapBuild([1,2,3,4,5]) as res;
2. Convert bitmap object to integer array
select bitmapToArray(bitmapBuild([1,2,3,4,5])) as res; /* ┌─res─────────┐ │ [1,2,3,4,5] │ └─────────────┘ */
3.bitmapSubsetInRange converts the specified range of bitmap into another bitmap, which is equivalent to intercepting data, closing on the left and opening on the right
select bitmapToArray(
bitmapSubsetInRange(
bitmapBuild(
[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]
),
toUInt32(10),
toUInt32(15)
)
) as res;
/*
┌─res──────────────┐
│ [10,11,12,13,14] │
└──────────────────┘
*/4.bitmapSubsetLimit converts the specified range of bitmap into another bitmap (bitmap function, starting point, limit number)
select bitmapToArray(bitmapSubsetLimit(bitmapBuild(
[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]),
toUInt32(10), toUInt32(15))) as res;
/*
┌─res────────────────────────────────────────────┐
│ [10,11,12,13,14,15,16,17,18,19,20,21,22,23,24] │
└────────────────────────────────────────────────┘
*/5.bitmapContains check whether the bitmap contains the specified element: if it exists, it returns 1; if it does not exist, it returns 0
select bitmapContains(bitmapBuild([1,2,3,4,5]), toUInt32(9)) as res;
6.bitmapHasAny compares whether two bitmaps contain the same elements: if there is the same, it returns 1, and if there is no, it returns 0
select bitmapHasAny(bitmapBuild([1,2,3]), bitmapBuild([3,4,5])) as res;
7.bitmapHasAll if the first bitmap contains the second bitmap, all elements return 1, otherwise return 0
select bitmapHasAll(bitmapBuild([1,2,3]), bitmapBuild([3,4,5])) as res;
8.bitmapAnd performs and operations on two bitmaps and returns a new bitmap object = "get intersection
select bitmapToArray(bitmapAnd(bitmapBuild([1,2,3,4,5,6]), bitmapBuild([3,4,5,6,7,8]))) as res; /* ┌─res───────┐ │ [3,4,5,6] │ └───────────┘ */
9.bitmapOr performs or operation on two bitmaps and returns a new bitmap object = "union set"
select bitmapToArray(bitmapOr(bitmapBuild([1,2,3,4,5,6]), bitmapBuild([3,4,5,6,7,8]))) as res; /* ┌─res───────────────┐ │ [1,2,3,4,5,6,7,8] │ └───────────────────┘ */
10.bitmapXor performs XOR operation on two bitmaps and returns a new bitmap object = "remove the duplicate values of the two and merge other values
select bitmapToArray(bitmapXor(bitmapBuild([1,2,3,4,5,6]), bitmapBuild([3,4,5,6,7,8]))) as res; /* ┌─res───────┐ │ [1,2,7,8] │ └───────────┘ */
11.bitmapAndnot calculates the difference between two bitmaps and returns a new bitmap object = > the numerical result of the first bitmap excluding the same value of the second bitmap
select bitmapToArray(bitmapAndnot(bitmapBuild([1,2,3,4,5,6]), bitmapBuild([3,4,5,6,7,8]))) as res; /* ┌─res───┐ │ [1,2] │ └───────┘ */
12.bitmapCardinality returns a value of type UInt64, indicating the number of bitmap object elements: the following number is 5
select bitmapCardinality(bitmapBuild([1,2,3,4,6])) as res;
13.bitmapMin returns a value of UInt64 type, indicating the minimum value of bitmap: 1
select bitmapMin(bitmapBuild([1,2,3,4,5,6,7,8])) as res;
14.bitmapMax returns a value of UInt64 type, indicating the maximum value of bitmap: 8
select bitmapMax(bitmapBuild([1,2,3,4,5,6,7,8])) as res;
15.bitmapAndCardinality and operate the two bitmaps to obtain the number of corresponding bitmap object elements: 4
SELECT bitmapAndCardinality(bitmapBuild([1,2,3,4,5,6]), bitmapBuild([3,4,5,6,7,8])) AS res;
16.bitmapOrCardinality two bitmaps are or operated to obtain the number of corresponding bitmap object elements: 8
SELECT bitmapOrCardinality(bitmapBuild([1,2,3,4,5,6]), bitmapBuild([3,4,5,6,7,8])) AS res;
17.bitmapXorCardinality performs XOR operation on two bitmaps to obtain the number of corresponding bitmap object elements: 4
SELECT bitmapXorCardinality(bitmapBuild([1,2,3,4,5,6,7]), bitmapBuild([3,4,5,6,7,8,9])) AS res;
18.bitmapAndnotCardinality bitmap and nonstandard bitmap. Calculate the difference between the two bitmaps and return the cardinality of the result bitmap: 2
SELECT bitmapAndnotCardinality(bitmapBuild([1,2,3,4,5,6,7]), bitmapBuild([3,4,5,6,7,8,9])) AS res;
19. Use of aggregate functions groupBitmapAnd, groupBitmapOr, groupBitmapXor
-- Data preparation
CREATE TABLE circle_db.bitmap_column_expr_test
(
tag_id String,
z AggregateFunction(groupBitmap, UInt32)
)
ENGINE = MergeTree
ORDER BY tag_id;
INSERT INTO circle_db.bitmap_column_expr_test
VALUES ('tag1', bitmapBuild(cast([1,2,3,4,5,6,7,8,9,10] as Array(UInt32))));
INSERT INTO circle_db.bitmap_column_expr_test
VALUES ('tag2', bitmapBuild(cast([6,7,8,9,10,11,12,13,14,15] as Array(UInt32))));
INSERT INTO circle_db.bitmap_column_expr_test
VALUES ('tag3', bitmapBuild(cast([2,4,6,8,10,12] as Array(UInt32))));
INSERT INTO circle_db.bitmap_column_expr_test
VALUES ('tag4', bitmapBuild(cast([2,4,6,8,10,12,12,10] as Array(UInt32))));
-- Query test
SELECT groupBitmapAnd(z)
FROM circle_db.bitmap_column_expr_test
WHERE like(tag_id, 'tag%');
/* The principle is that all bitmap objects are intersected to obtain the quantity,
┌─groupBitmapAnd(z)─┐
│ 3 │
└───────────────────┘
*/
SELECT bitmapToArray(groupBitmapAndState(z))
FROM circle_db.bitmap_column_expr_test
WHERE tag_id = 'tag4';
/* The data in the bitmap object is de duplicated and then converted to array
┌─bitmapToArray(groupBitmapAndState(z))─┐
│ [2,4,6,8,10,12] │
└───────────────────────────────────────┘
*/
SELECT groupBitmapOr(z)
FROM circle_db.bitmap_column_expr_test
WHERE like(tag_id, 'tag%');
/* All matching bitmap objects are merged and the number of duplicates is removed
┌─groupBitmapOr(z)─┐
│ 15 │
└──────────────────┘
*/
SELECT groupBitmapXor(z)
FROM circle_db.bitmap_column_expr_test
WHERE like(tag_id, 'tag%');
/* Remove the duplicate values of the two and merge other values (operate every two bitmap objects) to get the final number
┌─groupBitmapOr(z)─┐
│ 10 │
└──────────────────┘
*/ClickHouse quick start
Install ClickHouse
Mac OS
wget 'https://builds.clickhouse.com/master/macos/clickhouse' chmod a+x ./clickhouse ./clickhouse
Ubuntu
sudo apt-get install apt-transport-https ca-certificates dirmngr sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4 echo "deb https://repo.clickhouse.com/deb/stable/ main/" | sudo tee \ /etc/apt/sources.list.d/clickhouse.list sudo apt-get update sudo apt-get install -y clickhouse-server clickhouse-client sudo service clickhouse-server start clickhouse-client
reference resources: https://clickhouse.com/#quick-start
Command line reference
./clickhouse
Use one of the following commands:
clickhouse local [args] clickhouse client [args] clickhouse benchmark [args] clickhouse server [args] clickhouse extract-from-config [args] clickhouse compressor [args] clickhouse format [args] clickhouse copier [args] clickhouse obfuscator [args] clickhouse git-import [args] clickhouse keeper [args] clickhouse keeper-converter [args] clickhouse install [args] clickhouse start [args] clickhouse stop [args] clickhouse status [args] clickhouse restart [args] clickhouse static-files-disk-uploader [args] clickhouse hash-binary [args]
Start ClickHouse Server
$./clickhouse server
The default port number of Server is 8123
Application: Listening for http://127.0.0.1:8123
Client connection ClickHouse
bytedance$./clickhouse client
ClickHouse client version 21.12.1.8808 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.12.1 revision 54450.
Quick tips for clickhouse-client Interactive mode:
clickhouse-client
clickhouse-client --host=... --port=... --user=... --password=...
Enable multiline queries:
clickhouse-client -m clickhouse-client --multiline
Run queries in batch-mode:
clickhouse-client --query='SELECT 1'echo'SELECT 1'|clickhouse-clientclickhouse-client<<<'SELECT 1'
Insert data from a file in specified format:
clickhouse-client --query='INSERT INTO table VALUES'< data.txt clickhouse-client --query='INSERT INTO table FORMAT TabSeparated'< data.tsv
Create database
Table engine supported by ClickHouse: Ordinary/Dictionary/Memory/Mysql/Lazy
To create a database, specify the database engine syntax:
create database xxxx engine
Example:
1. Create a clickhouse database of the default engine:
create database mydb engine=Ordinary comment 'mydb';
The default engine is Ordinary. If you do not specify a database, the engine creates an Ordinary database
2. Create clickhouse database and use Mysql engine:
create database mysqlDB engine=MySQL('xx:3306','database','username','password');
3. Create the database of Lazy engine:
create database testlazy engine=Lazy(expiration_time_in_seconds);
Expiration since last visit_ time_ in_ Seconds before the table is put into memory
Only * Log table engines can be created under this library engine
Query all databases of the current server instance:
SELECT * FROM system.databases;
Query id: 3783b56d-d088-47e4-981a-57864a7a8419
┌─name───────────────┬─engine───┬─data_path────┬─metadata_path─────────────────────────────────────────────────────────┬─uuid─────────────────────────────────┬─comment─┐ │ INFORMATION_SCHEMA │ Memory │ ./ │ │ 00000000-0000-0000-0000-000000000000 │ │ │ default │ Atomic │ ./store/ │ /Users/bytedance/soft/store/456/456e7573-40ea-42f8-856e-757340ea82f8/ │ 456e7573-40ea-42f8-856e-757340ea82f8 │ │ │ information_schema │ Memory │ ./ │ │ 00000000-0000-0000-0000-000000000000 │ │ │ mydb │ Ordinary │ ./data/mydb/ │ /Users/bytedance/soft/metadata/mydb/ │ 00000000-0000-0000-0000-000000000000 │ mydb │ │ system │ Atomic │ ./store/ │ /Users/bytedance/soft/store/1db/1dbe01d6-a33f-46ae-9dbe-01d6a33f56ae/ │ 1dbe01d6-a33f-46ae-9dbe-01d6a33f56ae │ │ └────────────────────┴──────────┴──────────────┴───────────────────────────────────────────────────────────────────────┴──────────────────────────────────────┴─────────┘ 5 rows in set. Elapsed: 0.001 sec.
Build table
create table test ( dim_id String, tag_code String, tag_option_code String, tag_option_value String, object_ids Array(String), p_date DateTime ) engine =MergeTree partition by p_date order by (dim_id,tag_code,tag_option_code,p_date) ;
insert data
INSERT INTO mydb.test (dim_id, tag_code, tag_option_code, tag_option_value, object_ids, p_date) VALUES ('1', 't1', 'f1', 'a', null, '2021-11-23 17:19:29')Query data
select * from test;
