Sqids support in ClickHouse

[rschu1ze]

Hi,

I could not find a repository for the website on GitHub (and could not make a pull request) so I figured I could open this thread instead.

ClickHouse (a popular SQL database for real-time analytics) recently added a SQL function sqid() (docs, PR) which (not surprisingly) generate sqids from SQL, e.g. SELECT sqid(125, 126). If you feel it is appropriate, you might want to add ClickHouse to a section "Used in ..." on the website, similar to what the website for roaring bitmaps does.

[4kimov]

Hi @rschu1ze,

Thank you for the heads up. Yes, I saw that mentioned in ClickHouse's CHANGELOG a few days ago, pretty cool. We don't have a dedicated "used in other projects" section, but we do have a list of repos/code, so let me see how that can be added there (it has a particular structure that would have to be adjusted).

In the meantime, noticed a minor tidbit on the ClickHouse docs, where it says:

Returned Value: A hash id String.

Technically, Sqids encoded numbers are not a hash since the output can be decoded back into numbers, so we just refer to the output as IDs (or strings). Just thought it's worth a mention.

I'll leave this discussion open until ClickHouse is added to the website.

[rschu1ze]

Sure, thanks.

You are right about the docs, I fixed the wording here: ClickHouse/ClickHouse#58517

[4kimov]

Hi @rschu1ze,

Cool, thank you for adjusting the wording. ClickHouse page has been added here: https://sqids.org/clickhouse Do you see anything that should be improved or communicated better?

[rschu1ze]

Hi @4kimov,

very nice, thanks! The only thing you might want to improve is to add the ClickHouse logo (see https://github.com/ClickHouse/) here:

[4kimov]

Hi @rschu1ze,

Yeah, we are missing icons for a few languages - I'm going to try to address that within the next few releases. Thank you for the feedback, closing this one as done.

[4kimov]

Icon added.

[mgred]

@rschu1ze thanks for you work - awesome ❤️

Some thoughts that come to my mind: In other sqids projects, we use to have some function/struct/object exported named sqids or Sqids (plural) that gives methods to encode and decode.

Questions:

• Should the function better be named sqids?
• the sqid function in ClickHouse only encodes. Would there be a usecase to also decode?

[rschu1ze]

@mgred Thanks for the feedback.

By convention, SQL function names (at least in ClickHouse) are in singular, e.g. here there is farmHash64() but not farmHashes64().

About decoding, I added that in this PR.

[mgred]

Ah, nice! And you renamed to encodeSqid, I like that - makes it more specific 👍 Personally, I would go with sqidsEncode, just to follow the pattern, but have no clue about naming in ClickHouse and best practice.

Again thanks for sharing

BTW, I left a little comment 😉

[rschu1ze]

Cool, thanks again. You have a point about the word order, I switched to sqidEncode() and sqidDecode() (still using singular).

[rschu1ze]

@4kimov As ClickHouse/ClickHouse#58544 rolls in, you probably want to change the example on https://sqids.org/clickhouse to

SELECT sqidEncode(1, 2, 3); -- '86Rf07'
SELECT sqidDecode('86Rf07)'; -- [1, 2, 3]

[4kimov]

@rschu1ze Copy that. Subscribed to the PR, will update the site when this change goes out. Thanks.