alexeykupershtokh / clickhouse-maxmind-geoip Goto Github PK

I noticed that you do not have to split v4 and v6 in different dictionaries.

You can combine the v4 and v6 CSVs and still can lookup v4 and v6 addresses correctly. (Would reduce the dictionaries you need)

There's a possibility to use some more efficient data types for dictionaries' fields like is_anonymous_proxy or represented_country_geoname_id

For example:

CREATE TABLE test_ip (
  `id` Int32,
  `ip` Nullable(String)
) ENGINE = MergeTree() ORDER BY (id)

INSERT INTO test_ip VALUES
    (1, '174.105.199.64'),
    (2, '40.107.219.92'),
    (3, '40.107.219.59'),
    (4, '65.246.27.210'),
    (5, '50.98.35.219'),
    (6, '70.67.156.137')

If try get data from dictionaries

SELECT 
    ip,
    dictGet('geoip_city_blocks_ipv4', 'geoname_id', tuple(IPv4StringToNum(ip))) AS geoname_id
FROM 
    test_ip

Request getting error:

Code: 53, e.displayText() = DB::Exception: Key does not match, expected either UInt32 or FixedString(16)

If change type just to String, the same request work correctly, do you know how to fix it without changing field type? Thanks

There's a possibility to query dictionaries like regular tables and join them: https://clickhouse.yandex/docs/en/operations/table_engines/dictionary/

Queries to join dictionaries to be provided

Does the ClickHouse dictionary support.mmdb files? Rather than CSV。

A project without it can't be used legally.

As of March 2022 these files (and maybe more) have a slightly different csv header to the ones they had when this project was created.

eg (note the very first field)
GeoLite2-City-Blocks-IPv4.csv
network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider,postal_code,latitude,longitude,accuracy_radius
1.0.0.0/24,2077456,2077456,,0,0,,-33.4940,143.2104,1000
1.0.1.0/24,1814991,1814991,,0,0,,34.7732,113.7220,1000
...

I believe these files are effected:
/clickhouse/geoip_asn_blocks_ipv6_dictionary.xml
/clickhouse/geoip_asn_blocks_ipv4_dictionary.xml
/clickhouse/geoip_country_blocks_ipv6_dictionary.xml
/clickhouse/geoip_country_blocks_ipv4_dictionary.xml
/clickhouse/geoip_city_blocks_ipv4_dictionary.xml
/clickhouse/geoip_city_blocks_ipv6_dictionary.xml

Happy to submit a pull if you'd like.

Hi, first of all, thanks for this example!

I made a PR to address an issue I ran into when upgrading Clickhouse from version 19 to version 21. The import from CSV stopped working because the escaping of apostrophes apparently changed.

Luckily, there is a format option for Clickhouse CSV that I think is an even cleaner solution.

#5