Add new DataType Map(key,value) by hexiaoting · Pull Request #15806 · ClickHouse/ClickHouse

hexiaoting · 2020-10-10T07:15:37Z

I hereby agree to the terms of the CLA available at: https://yandex.ru/legal/cla/?lang=en

Changelog category (leave one):

New Feature

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):
Add new datatype Map for supporting storage k:v .
related to issue: #1841
1st version for Map only support String type of key and value.
Later I will implement Int and other type for key and value.

Detailed description / Documentation draft:
Add new datatype Map for supporting storage k:v .
related to issue: #1841
1st version for Map only support String type of key and value.
Later I will implement Int and other type for key and value.
Usage:

create table table_map (a Map(String, String)) engine Memory;
insert into table_map values ({'name':'zhangsan', 'gender':'male'}), ({'name':'lisi', 'gender':'female'});
select a['name'] from table_map;

Addition:

PostgreSQL Json data type

postgresql does not support map data type but json, Operator for json in PostgreSQL:

Operator	Right Operand Type	Description	Example
->	int	Get JSON array element	'[1,2,3]'::json->2
->	text	Get JSON object field	'{"a":1,"b":2}'::json->'b'
->>	int	Get JSON array element as text	'[1,2,3]'::json->>2
->>	text	Get JSON object field as text	'{"a":1,"b":2}'::json->>'b'
#>	array of text	Get JSON object at specified path	'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'
#>>	array of text	Get JSON object at specified path as text	'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

And postgreSQL support other json functions like : xxx_to_json, json_each....

Hive Map data type

Usage:

create table map_test ( metrics MAP <STRING, BIGINT>);

desc map_test.metrics;
+-----------+------------------+--------------------+--+
| col_name  |    data_type     |      comment       |
+-----------+------------------+--------------------+--+
| metrics   | map<string,int>  | from deserializer  |
+-----------+------------------+--------------------+--+

And Hive does not support literals for complex types (array, map, struct, union)
select * from map_test;
{"abc": 123}

hexiaoting · 2020-10-13T10:12:16Z

@CurtizJ
Hi, I build these codes in my environment successfully with gcc10 or clang11.
How can I reproduce the build error in "ClickHouse build check" in my environment?

CurtizJ · 2020-10-13T12:26:50Z

You can try to merge with fresh master.

/build/src/Functions/FunctionsConversion.h:2201:19: error: no member named 'getByPosition' in 'std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>>'
Seems that error appears because recently Block from Block.h was replaced to ColumnsWithTypeAndName in functions (here)

ildus · 2020-10-13T15:40:35Z

Have you considered using separate columns for each key? In that case it would be easy to implement combinators and such stuff, since then it would be no need to collect values by keys first.

hexiaoting · 2020-10-15T02:18:51Z

Have you considered using separate columns for each key? In that case it would be easy to implement combinators and such stuff, since then it would be no need to collect values by keys first.

In the first implement version for Map Datatype, I use tow array columns, one for key and the other for value. I think it's the simplest way to implement this.
If the operator of combinators in Map type is needed by many users, I will try to implement it in later version.

hexiaoting · 2020-10-19T03:04:55Z

@CurtizJ I have finished coding. Can you review it when free? Thank you.

CurtizJ · 2020-10-21T22:21:41Z

Yes, I will try to review your PR by the end of the week.

BTW, please, add tests with other table engines, at least with MergeTree, because storage Memory doesn't use serialization/deserialization from IDataType and it's not checked. I've created a MergeTree table with type Map and got segfault on insert.

CREATE TABLE table_map(a Map(String, String)) ENGINE = MergeTree ORDER BY tuple();

insert into table_map values ({'name':'zhangsan', 'gender':'male'}), ({'name':'lisi', 'gender':'female'});

stacktrace

2020.10.22 01:01:29.264497 [ 41075 ] {} <Fatal> BaseDaemon: ########################################
2020.10.22 01:01:29.264648 [ 41075 ] {} <Fatal> BaseDaemon: (version 20.11.1.4942, build id: DB5B18ED166F29B8) (from thread 40789) (query_id: ec9d1905-25c1-48b6-bea9-1b82849f3c01) Received signal Segmentation fault (11)
2020.10.22 01:01:29.264685 [ 41075 ] {} <Fatal> BaseDaemon: Address: 0x98 Access: read. Address not mapped to object.
2020.10.22 01:01:29.264724 [ 41075 ] {} <Fatal> BaseDaemon: Stack trace: 0x7bbe247 0xd7ac1ce 0xd82bc01 0xe3f0807 0xe3f02fe 0xe3f09e5 0xe4b2e5c 0xe42b50c 0xe34ba8c 0xdbf0184 0xdc31043 0xdc2fdcc 0xdc2fe49 0xe5e9c40 0xe5e52a9 0xe5f1db7 0x10dc8def 0x10dca7fe 0x10efbab9 0x10ef79ea 0x7fb0857da6db 0x7fb0850f788f
2020.10.22 01:01:29.264828 [ 41075 ] {} <Fatal> BaseDaemon: 2. DB::WriteBuffer::write(char const*, unsigned long) @ 0x7bbe247 in /usr/bin/clickhouse
2020.10.22 01:01:29.264904 [ 41075 ] {} <Fatal> BaseDaemon: 3. DB::DataTypeArray::serializeBinaryBulkWithMultipleStreams(DB::IColumn const&, unsigned long, unsigned long, DB::IDataType::SerializeBinaryBulkSettings&, std::__1::shared_ptr<DB::IDataType::SerializeBinaryBulkState>&) const @ 0xd7ac1ce in /usr/bin/clickhouse
2020.10.22 01:01:29.264937 [ 41075 ] {} <Fatal> BaseDaemon: 4. DB::DataTypeMap::serializeBinaryBulkWithMultipleStreams(DB::IColumn const&, unsigned long, unsigned long, DB::IDataType::SerializeBinaryBulkSettings&, std::__1::shared_ptr<DB::IDataType::SerializeBinaryBulkState>&) const @ 0xd82bc01 in /usr/bin/clickhouse
2020.10.22 01:01:29.265011 [ 41075 ] {} <Fatal> BaseDaemon: 5. DB::MergeTreeDataPartWriterCompact::writeColumnSingleGranule(DB::ColumnWithTypeAndName const&, std::__1::function<DB::WriteBuffer* (std::__1::vector<DB::IDataType::Substream, std::__1::allocator<DB::IDataType::Substream> > const&)>, unsigned long, unsigned long) @ 0xe3f0807 in /usr/bin/clickhouse
2020.10.22 01:01:29.265059 [ 41075 ] {} <Fatal> BaseDaemon: 6. DB::MergeTreeDataPartWriterCompact::writeBlock(DB::Block const&) @ 0xe3f02fe in /usr/bin/clickhouse
2020.10.22 01:01:29.265086 [ 41075 ] {} <Fatal> BaseDaemon: 7. DB::MergeTreeDataPartWriterCompact::finishDataSerialization(DB::MergeTreeDataPartChecksums&, bool) @ 0xe3f09e5 in /usr/bin/clickhouse
2020.10.22 01:01:29.265122 [ 41075 ] {} <Fatal> BaseDaemon: 8. DB::MergedBlockOutputStream::writeSuffixAndFinalizePart(std::__1::shared_ptr<DB::IMergeTreeDataPart>&, bool, DB::NamesAndTypesList const*, DB::MergeTreeDataPartChecksums*) @ 0xe4b2e5c in /usr/bin/clickhouse
2020.10.22 01:01:29.265155 [ 41075 ] {} <Fatal> BaseDaemon: 9. DB::MergeTreeDataWriter::writeTempPart(DB::BlockWithPartition&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&) @ 0xe42b50c in /usr/bin/clickhouse
2020.10.22 01:01:29.265181 [ 41075 ] {} <Fatal> BaseDaemon: 10. DB::MergeTreeBlockOutputStream::write(DB::Block const&) @ 0xe34ba8c in /usr/bin/clickhouse
2020.10.22 01:01:29.265208 [ 41075 ] {} <Fatal> BaseDaemon: 11. DB::PushingToViewsBlockOutputStream::write(DB::Block const&) @ 0xdbf0184 in /usr/bin/clickhouse
2020.10.22 01:01:29.265237 [ 41075 ] {} <Fatal> BaseDaemon: 12. DB::AddingDefaultBlockOutputStream::write(DB::Block const&) @ 0xdc31043 in /usr/bin/clickhouse
2020.10.22 01:01:29.265266 [ 41075 ] {} <Fatal> BaseDaemon: 13. DB::SquashingBlockOutputStream::finalize() @ 0xdc2fdcc in /usr/bin/clickhouse
2020.10.22 01:01:29.265282 [ 41075 ] {} <Fatal> BaseDaemon: 14. DB::SquashingBlockOutputStream::writeSuffix() @ 0xdc2fe49 in /usr/bin/clickhouse
2020.10.22 01:01:29.265314 [ 41075 ] {} <Fatal> BaseDaemon: 15. DB::TCPHandler::processInsertQuery(DB::Settings const&) @ 0xe5e9c40 in /usr/bin/clickhouse
2020.10.22 01:01:29.265339 [ 41075 ] {} <Fatal> BaseDaemon: 16. DB::TCPHandler::runImpl() @ 0xe5e52a9 in /usr/bin/clickhouse
2020.10.22 01:01:29.265372 [ 41075 ] {} <Fatal> BaseDaemon: 17. DB::TCPHandler::run() @ 0xe5f1db7 in /usr/bin/clickhouse
2020.10.22 01:01:29.265400 [ 41075 ] {} <Fatal> BaseDaemon: 18. Poco::Net::TCPServerConnection::start() @ 0x10dc8def in /usr/bin/clickhouse
2020.10.22 01:01:29.265417 [ 41075 ] {} <Fatal> BaseDaemon: 19. Poco::Net::TCPServerDispatcher::run() @ 0x10dca7fe in /usr/bin/clickhouse
2020.10.22 01:01:29.265442 [ 41075 ] {} <Fatal> BaseDaemon: 20. Poco::PooledThread::run() @ 0x10efbab9 in /usr/bin/clickhouse
2020.10.22 01:01:29.265471 [ 41075 ] {} <Fatal> BaseDaemon: 21. Poco::ThreadImpl::runnableEntry(void*) @ 0x10ef79ea in /usr/bin/clickhouse
2020.10.22 01:01:29.265502 [ 41075 ] {} <Fatal> BaseDaemon: 22. start_thread @ 0x76db in /lib/x86_64-linux-gnu/libpthread-2.27.so
2020.10.22 01:01:29.265613 [ 41075 ] {} <Fatal> BaseDaemon: 23. /build/glibc-OTsEL5/glibc-2.27/misc/../sysdeps/unix/sysv/linux/x86_64/clone.S:97: __clone @ 0x12188f in /usr/lib/debug/lib/x86_64-linux-gnu/libc-2.27.so

hexiaoting · 2020-10-22T06:04:42Z

@CurtizJ Ok, I will add tests with MergeTree Engine

CurtizJ

Also need to implement function map (similar to array and tuple) to allow creation of maps not only for literals.

To allow something like this:

insert into table_map(m) select {'key1' : number, 'key2' : number * 2} from numbers(1000)

insert into table_map(m) select map('key1', number, 'key2', number * 2) from numbers(1000)

CurtizJ · 2020-10-22T18:27:18Z

src/Functions/array/arrayElement.cpp

+        }
+        else
+        {
+            // Default value for unmatched keys


It's better to use default value of type.

how to distinguish the real value and the default value?
for example, Integer type's default value is 0

CurtizJ · 2020-10-22T18:29:09Z

src/Functions/array/arrayElement.cpp

+    const DataTypePtr & key_type = (typeid_cast<const DataTypeArray *>(kv_types[0].get()))->getNestedType();
+    const DataTypePtr & value_type = (typeid_cast<const DataTypeArray *>(kv_types[1].get()))->getNestedType();
+
+    Field index = (*columns[arguments[1]].column)[0];


Seems, that only const indices are supported. Need to add check for constness of argument. But it's better to implement support of non-const columns as index, like it's done in arrayElement function.

ok, I will improve this.

CurtizJ · 2020-10-22T18:30:17Z

src/Functions/array/arrayElement.cpp

+}
+
+template <typename DataType>
+bool FunctionArrayElement::executeMappedValueNumber(const ColumnArray * column, std::vector<int> matched_idxs,


Need a const reference for vector.

CurtizJ · 2020-10-22T18:32:34Z

src/Functions/array/arrayElement.cpp

+    const ColumnArray::Offsets & offsets = column->getOffsets();
+    size_t rows = offsets.size();
+
+    for (size_t i = 0; i < rows; i++)


Since you know number of elements, it's better to do reserve for vector before loop for better performance.

CurtizJ · 2020-10-26T14:57:33Z

src/DataTypes/DataTypeMap.cpp

+std::string DataTypeMap::doGetName() const
+{
+    WriteBufferFromOwnString s;
+    s << "Map(" << (typeid_cast<const DataTypeArray *>(keys.get()))->getNestedType()->getName()


No need to typeid_cast here. You have already saved key_type and value_type.

Enmk · 2020-10-27T14:52:10Z

src/Columns/ColumnMap.cpp

+ColumnMap::ColumnMap(MutableColumns && mutable_columns)
+{
+    columns.reserve(mutable_columns.size());
+    for (auto & column : mutable_columns)


Here you can init ColumnMap::columns to have less than 2 columns, that would cause a crash at run-time in say ColumnMap::cloneEmpty() or any other method that assumes that there are exactly 2 columns.

Consider checking that each of mutable_columns is actually a ColumnArray.

SELECT CAST(([1, 2, 3], ['1', '2', 'foo']), 'Map(UInt8, String)') AS map

akuzm · 2020-10-28T10:25:36Z

src/DataTypes/DataTypeMap.cpp

+
+void DataTypeMap::serializeTextJSON(const IColumn & column, size_t row_num, WriteBuffer & ostr, const FormatSettings & settings) const
+{
+    writeChar('[', ostr);


It would probably be more natural to serialize Map as a JSON object.

akuzm · 2020-10-28T10:26:38Z

src/Core/Field.cpp

+        UInt8 type;
+        DB::readBinary(type, buf);
+
+        switch (type)


Consider using Field::dispatch + overloaded functions, to avoid enumerating all Field types in many places.

As an example, see writeFieldText or toString(Field) in Field.cpp.

Allow casting Tuple as Map.

hexiaoting · 2020-11-03T03:27:54Z

@akuzm @CurtizJ @Enmk
Now in the latest implementation, Map's usage includes:

insert into table_map(m) select {'key1' : number, 'key2' : number * 2} from numbers(1000)
insert into table_map(m) select map('key1', number, 'key2', number * 2) from numbers(1000)

But this is repeated with query parameter.

From the test I found the syntax of DataType Map is confused with below test cases:

01526_param_uuid's test case is :

${CLICKHOUSE_CLIENT} --param_p1='ffffffff-ffff-ffff-ffff-ffffffffffff' --query "SELECT {p1:UUID}"

01342_query_parameters_alias:

$CLICKHOUSE_CLIENT --param_x '\N' --query 'SELECT {x:Nullable(Nothing)} as a' --format TSVWithNamesAndTypes

3)01056_prepared_statements_null_and_escaping.sh

${CLICKHOUSE_CURL} -sS "${CLICKHOUSE_URL}&param_x=Hello,%20World" \
    -d "SELECT {x:Nullable(String)}";
......

4)01015_insert_values_parametrized.sh

$CLICKHOUSE_CLIENT --input_format_values_deduce_templates_of_expressions=0 --input_format_values_interpret_expressions=1 --param_p_n="-1" --param_p_s="param" --param_p_a="[0.2,0.3]" --query="INSERT INTO insert_values_parametrized  VALUES \
(5 + {p_n:Int8}, lower(concat('Evaluate', {p_s:String})), arrayIntersect([0, 0.2, 0.6], {p_a:Array(Nullable(Float32))}))

00956_http_prepared_statements

${CLICKHOUSE_CURL} -sS "${CLICKHOUSE_URL}&param_id=1" \
    -d "SELECT * FROM ps WHERE i = {id:UInt8} ORDER BY i, s, d";

00955_complex_prepared_statements

$CLICKHOUSE_CLIENT --max_threads=1 --param_aui="[1, 2]" \
    -q "SELECT t FROM ps WHERE a = {aui:Array(UInt16)}";

7)00954_client_prepared_statements.sh

$CLICKHOUSE_CLIENT --max_threads=1 --param_phrase='Hello, world' \
    -q "SELECT * FROM ps WHERE s = {phrase:String}";

But

SELECT {p1:UUID}
SELECT {x:Nullable(Nothing)} as a
......

can also be parsed as Map .
How can I solve this problem?

…nto dev_map2

hexiaoting · 2020-11-11T02:33:10Z

@CurtizJ ready for review.

hexiaoting · 2020-12-01T08:46:17Z

@alexey-milovidov @CurtizJ
hi, right now this PR is ready to review.
In this version, data in Map DataType is stored using two columns, one for storing keys and the other is for storing values. When using map['key'] to get the value I use sequential iterate method, Of course this is the slowest way。

After this PR is reviewed, in the next stage:

@Enmk is trying to implement index based lookup method to speedup.
consider using separate columns for each key to make the Map DataType directly columnar storage.

CurtizJ · 2020-12-01T11:58:05Z

Thanks. I will review and merge soon.

hexiaoting · 2020-12-11T08:53:10Z

This PR is moved to #17829

Merging data type Map #15806

meshpaul · 2020-12-29T01:54:29Z

🥇

ahutsunshine · 2021-01-07T07:36:49Z

@Enmk @CurtizJ @akuzm May I ask when and what's the plan to build a new version to release the feature?

wangxin05 · 2022-02-22T12:33:10Z

@alexey-milovidov @CurtizJ hi, right now this PR is ready to review. In this version, data in Map DataType is stored using two columns, one for storing keys and the other is for storing values. When using map['key'] to get the value I use sequential iterate method, Of course this is the slowest way。

After this PR is reviewed, in the next stage:

@Enmk is trying to implement index based lookup method to speedup.

consider using separate columns for each key to make the Map DataType directly columnar storage.

Hi,
Thank you so much for providing this great data type. And by the way, what is the current progress of the second item(consider using separate columns for each key to make the Map DataType directly columnar storage.)?

hexiaoting · 2022-02-23T02:29:29Z

@wangxin05 maybe this feature will be implemented in #23932

robot-clickhouse added doc-alert pr-feature Pull request with new product feature labels Oct 10, 2020

alexey-milovidov added the can be tested label Oct 10, 2020

CurtizJ self-assigned this Oct 12, 2020

CurtizJ added the force tests label Oct 12, 2020

hexiaoting added 5 commits October 14, 2020 14:35

Add new DataType Map(key,value)

cdb4dd5

Fix style check errors.

974352a

Fix style check error.

03102fe

Fix build error

4f3f221

Update with fresh master to build sucessfully.

cb9e88e

hexiaoting force-pushed the dev_map2 branch from 20f1f49 to cb9e88e Compare October 14, 2020 08:01

hexiaoting added 3 commits October 17, 2020 23:45

Implement Integer And Array for value type

db8ba39

Merge branch 'master' into dev_map2

fcd35b0

Fix build error

521b07d

Support MergeTree Engine

483be13

CurtizJ reviewed Oct 26, 2020

View reviewed changes

Enmk mentioned this pull request Oct 26, 2020

Column map fixes #16405

Closed

Enmk reviewed Oct 27, 2020

View reviewed changes

Allow casting Tuple as Map.

e0d85ff

SELECT CAST(([1, 2, 3], ['1', '2', 'foo']), 'Map(UInt8, String)') AS map

akuzm reviewed Oct 28, 2020

View reviewed changes

hexiaoting added 2 commits October 28, 2020 19:13

Merge pull request #1 from Enmk/ColumnMap_fixes

ceab1ad

Allow casting Tuple as Map.

Add map function

2ba68d7

hexiaoting added 5 commits November 2, 2020 14:07

Merge remote-tracking branch 'hexiaoting/dev_map2' into dev_map2

619fe6b

Update codes

e1ff6c3

Merge remote-tracking branch 'origin/master' into dev_map2

3a70cf3

Add test case for tuple convert to map

3fe6f1e

Style error fix

66bca89

remove map literal and Merge remote-tracking branch 'origin/master' i…

28e87da

…nto dev_map2

hexiaoting marked this pull request as draft November 6, 2020 09:19

hexiaoting added 2 commits November 6, 2020 17:21

style error fix

ef0b78c

fix bug

c7c14cc

hexiaoting marked this pull request as ready for review November 10, 2020 03:26

hexiaoting added 2 commits November 26, 2020 15:13

Merge remote-tracking branch 'origin/master' into dev_map2

74d875c

Merge remote-tracking branch 'origin/master' into dev_map2

5b3a7f5

This was referenced Nov 26, 2020

proposal for a function to reduce boilerplate with select nested #3765

Closed

Roadmap 2021 (discussion) #17623

Closed

CurtizJ added a commit that referenced this pull request Dec 16, 2020

Merge pull request #17829 from CurtizJ/merging-map

b1dc807

Merging data type Map #15806

CurtizJ merged commit 12604ce into ClickHouse:master Dec 16, 2020

alexey-milovidov mentioned this pull request Jan 11, 2021

Map datatype #1841

Closed

olgarev mentioned this pull request Jan 25, 2021

DOCSUP-5823: Documented Map(key,value) type and map() function. #19592

Merged

ahutsunshine mentioned this pull request Jan 26, 2021

How to get map type all keys or values or do count aggregation? #19611

Closed

spz0507 mentioned this pull request Feb 2, 2021

Is Map Support ? ClickHouse/clickhouse-java#561

Closed

Conversation

hexiaoting commented Oct 10, 2020 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Addition:

PostgreSQL Json data type

Hive Map data type

Uh oh!

hexiaoting commented Oct 13, 2020 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

CurtizJ commented Oct 13, 2020

Uh oh!

ildus commented Oct 13, 2020

Uh oh!

hexiaoting commented Oct 15, 2020

Uh oh!

hexiaoting commented Oct 19, 2020

Uh oh!

CurtizJ commented Oct 21, 2020

Uh oh!

hexiaoting commented Oct 22, 2020

Uh oh!

CurtizJ left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

hexiaoting commented Nov 3, 2020 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

hexiaoting commented Nov 11, 2020

Uh oh!

hexiaoting commented Dec 1, 2020

Uh oh!

CurtizJ commented Dec 1, 2020

Uh oh!

hexiaoting commented Dec 11, 2020

Uh oh!

meshpaul commented Dec 29, 2020

Uh oh!

ahutsunshine commented Jan 7, 2021

Uh oh!

wangxin05 commented Feb 22, 2022

Uh oh!

hexiaoting commented Feb 23, 2022

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

10 participants

hexiaoting commented Oct 10, 2020 •

edited

Loading

hexiaoting commented Oct 13, 2020 •

edited

Loading

hexiaoting commented Nov 3, 2020 •

edited

Loading