How to create a query on ClickHouse similarly to a MySQL query that uses LIKE with JOIN?-CodePudding

I am aware that in ClickHouse we must use the equality operator with its ON conditionals. Any suggestion to solve cases where we have to use a LIKE operator, similarly what is done with MySQL?

Something like:

SELECT *
  FROM TABLE a
  JOIN TABLE b ON b.column LIKE CONCAT('%', a.column ,'%')

Just for testing, I have tried to use also a second conditional similar to the first example of the JOIN documentation:

SELECT course_id, usage_key , display_name  
  FROM video_events  LEFT JOIN video_info 
  ON video_events.course_id = video_info.course_key 
  AND startsWith(video_info.display_name, 'Choro');

but I get the error:

Code: 403. DB::Exception: Received from localhost:9000. DB::Exception: Expected equality or inequality, got 'startsWith(display_name, 'Choro')': While processing startsWith(display_name, 'Choro').

Even though I have a row with the string above:

e19b1e00a6b6 :) select course_id, usage_key , display_name  from video_events  JOIN video_info on video_events.course_id = video_info.course_key;

┌─────course_id───────────────────────────┬─usage_key─────────────────────────────────────────────────────────────────────────────────┬─display_name───────────────┐    
│ course-v1:Introduction CS001 2021autumn │ block-v1:Introduction CS001 2021autumn type@chapter block@379e3fbc409744d9bdaffa309dd6e20d    │ Section 1                  │
│ course-v1:Introduction CS001 2021autumn │ block-v1:Introduction CS001 2021autumn type@sequential block@e09c8c6e1ec746d1a31b634438f42071 │ Videos             │
│ course-v1:Introduction CS001 2021autumn │ block-v1:Introduction CS001 2021autumn type@vertical block@ac5ab25121044b1da4e9658e2d876a4b   │ Classical          │
│ course-v1:Introduction CS001 2021autumn │ block-v1:Introduction CS001 2021autumn type@video block@ec9ba45b09e74ee98858ee40e28e31e9      │ Debussy            │
│ course-v1:Introduction CS001 2021autumn │ block-v1:Introduction CS001 2021autumn type@vertical block@7799a7acf46d46e0a3b1b1298d6e542b   │ Brasileira         │
│ course-v1:Introduction CS001 2021autumn │ block-v1:Introduction CS001 2021autumn type@video block@7b91e95676eb472d8f528d701c5fe929      │ Choro              │
│ course-v1:Introduction CS001 2021autumn │ block-v1:Introduction CS001 2021autumn type@sequential block@a1586de2d0ca4b31ae24cf3146e09c96 │ Forum              │
└─────────────────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────────┘

I want to filter in the above string column usage_key of the table video_info an ID (e.g. 7799a7acf46d46e0a3b1b1298d6e542b) from the video_id column of the table video_events.

I also thought about using the match for searching strings, but I still couldn't figure out how to use it with the ON conditional.

CodePudding user response：

Try this one:

SELECT * 
FROM (
  SELECT course_id, usage_key , display_name  
  FROM video_events  LEFT JOIN video_info 
    ON video_events.course_id = video_info.course_key 
  )
WHERE startsWith(display_name, 'Choro');

CodePudding user response：

I have found a way to solve what I wanted by using the replaceRegexpOne function to filter the column that had the IDs of the other table so that I could use the equality when joining both tables.

So I got a query like this:

SELECT course_id, user_id, replaceRegexpOne(usage_key, '.*(video\ block@)(.*)', '\\2') AS video_info_id, display_name 
  FROM video_info 
  INNER JOIN video_events 
  ON video_info_id = video_id ;

which return this table with the columns course_id, user_id, video_id and video_display_name:

course-v1:Introduction CS001 2021autumn 11  ec9ba45b09e74ee98858ee40e28e31e9    Debussy
course-v1:Introduction CS001 2021autumn 11  ec9ba45b09e74ee98858ee40e28e31e9    Debussy
course-v1:Introduction CS001 2021autumn 10  7b91e95676eb472d8f528d701c5fe929    Choro
course-v1:Introduction CS001 2021autumn 10  7b91e95676eb472d8f528d701c5fe929    Choro