Cairn - How can I import data from MySQL to Clickhouse

Site Operators
,
1

I migrated new table in MySQL. How can I import data of this table from MySQL to Clickhouse

2

Hi @devpt

Welcome to the community.

I believe these can help, GitHub - overhangio/tutor-cairn: Scalable, real-time analytics for Open edX

You can also run these queries directly in shell by going in the service tutor local run clickhouse bash and then going to db and running these queries you can see if they indeed bring the data or not.

If they do you can create a migration for them.

1 Like
3

So, do I have to access to MySql database to get the data and then use “clickhouse bash” to import these data to clickhouse db?..

4

No, you can connect to mysql from clickhouse

You can do it like this

tutor local run clickhouse bash
clickhouse-client -u YOURUSERNAME --password YOURPASSWORD

and after that you can fire the queries from clickhouse client to get the data.

You can take query references from tutor-cairn/tutorcairn/templates/cairn/apps/clickhouse/migrations.d/0002_grades.sql at master · overhangio/tutor-cairn · GitHub

5

It’s worked. I’ve create new tabled successfully by running this command in clickhouse-client bash

CREATE TABLE school
(
    `name` String
)
ENGINE = MySQL('mysql:3306', 'openedx', 'student_school', 'openedx', 'xxxxxx');
-- Grant everyone access to the view
CREATE ROW POLICY common ON school FOR SELECT USING 1 TO ALL

I can query in SQL Lab now, but in my TUTOR_ROOT folder, I can’t find the migrate file. In “/env/plugins/cairn/apps/clickhouse/migrations.d”, there are only:

  • 0001_create.sql
  • 0002_grades.sql
  • 0003_course_enrollments.sql
  • 0004_video_views.sql
  • 0005_course_blocks.sql
  • 0006_course_block_completion.sql
  • 0007_fix_video_segments.sql
  • 0008_rename_openedx_tables.sql
  • 0009_add_graded_column_to_course_blocks.sql

Is this OK? Thank you

1 Like
6

And if I want to add column to a existed Table like “_openedx_user_profiles”, how can I do that?

7

Hi @devpt

You can easily import any table data from MySQL to ClickHouse by implementing simple steps:

Steps To Import Data From MySQL to ClickHouse with Crain SuperSet