alexeykupershtokh / clickhouse-maxmind-geoip Goto Github PK
View Code? Open in Web Editor NEWA demonstration how to use ClickHouse with MaxMind GeoIP2 databases for geolocaiton
License: MIT License
A demonstration how to use ClickHouse with MaxMind GeoIP2 databases for geolocaiton
License: MIT License
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.