1.
MySQL :: Other MySQL Documentation から “airportdb database (large dataset, intended for MySQL on OCI and HeatWave)” を探してダウンロード。HeatWave用って書いてあるけど別にVanillaでもOK。
$ wget https://downloads.mysql.com/docs/airport-db.tar.gz
--2024-04-18 00:43:20-- https://downloads.mysql.com/docs/airport-db.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 23.51.143.41, 2600:140b:2:99d::2e31, 2600:140b:2:99c::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.51.143.41|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 655687708 (625M) [application/x-gzip]
Saving to: 'airport-db.tar.gz'
100%[==============================================================================================================================================>] 655,687,708 11.5MB/s in 52s
2024-04-18 00:44:12 (12.0 MB/s) - 'airport-db.tar.gz' saved [655687708/655687708]
2. 得られたファイルを展開します。600MBくらいあったけど展開は速かった。
$ time tar xf airport-db.tar.gz
real 0m5.134s
user 0m4.424s
sys 0m1.362s
3. `airport-db` ディレクトリの下に色々ぶら下がってるので拝見。
$ ll
total 641612
-rwxr-xr-x 1 yoku0825 yoku0825 2586 Apr 1 07:05 @.done.json
-rwxr-xr-x 1 yoku0825 yoku0825 859 Apr 1 07:05 @.json
-rwxr-xr-x 1 yoku0825 yoku0825 46349 Apr 1 07:05 @.manifest.json
-rwxr-xr-x 1 yoku0825 yoku0825 240 Apr 1 07:05 @.post.sql
-rwxr-xr-x 1 yoku0825 yoku0825 240 Apr 1 07:05 @.sql
-rwxr-xr-x 1 yoku0825 yoku0825 4884 Apr 1 07:05 README.txt
-rwxr-xr-x 1 yoku0825 yoku0825 1199 Apr 1 07:05 airportdb.json
-rwxr-xr-x 1 yoku0825 yoku0825 583 Apr 1 07:05 airportdb.sql
..
-rwxr-xr-x 1 yoku0825 yoku0825 1752 Apr 1 07:05 airportdb@weatherdata@0.tsv.zst.idx
-rwxr-xr-x 1 yoku0825 yoku0825 4733492 Apr 1 07:05 airportdb@weatherdata@@1.tsv.zst
-rwxr-xr-x 1 yoku0825 yoku0825 240 Apr 1 07:05 airportdb@weatherdata@@1.tsv.zst.idx
見るからにMySQL ShellでダンプされたやつなのでMySQL Shellでインポートが必要。
目立たないREADME.txtの最後にインポート方法が書いてある。
いかにもHeatWave用のはVanillaでは転けるから、実行しないかまたは叩いて転けさせれば良いと思う。
使う関数は util.loadDump .
MySQL localhost SQL > SET GLOBAL local_infile = 1; -- util.loadDumpはlocal_infile=1 必須
Query OK, 0 rows affected (0.0007 sec)
MySQL localhost SQL > \js -- JSモード変更(pyでもいい)
Switching to JavaScript mode...
MySQL localhost JS > util.loadDump("./", {deferTableIndexes: "all", ignoreVersion: true})
Loading DDL and Data from './' using 4 threads.
Opening dump...
NOTE: Dump format has version 1.0.2 and was created by an older version of MySQL Shell. If you experience problems loading it, please recreate the dump using the current version of MySQL Shell and try again.
Target is MySQL 8.0.36. Dump was produced from MySQL 8.0.26-cloud
NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.
You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.
Scanning metadata - done
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
4 thds loading / 15% (317.41 MB / 2.03 GB), 9.49 MB/s, 12 / 14 tables done
..
util.loadDumpの第一引数はトップディレクトリへの相対パスなので、さっきもぐりこんだところがまさにそのディレクトリ = “./“ にした。他はリソースに余力があれば “threads”: 20, とか入れれば良いのだと思う（速くなるんですよこれがびっくりに）
ConoHaの2コア1GBメモリのプラン だけど割とすぐ20分もかからず終わったはず（ただし俺は今たぶん innodb_flush_log_at_trx_commit = 0, sync_binlog = 0してある）
突っ込んだ直後なのでANALYZE TABLEでもかけておきましょうか。
面倒なので mysqlcheck —analyze —all-databases で全部にかけました
$ mysqlcheck --analyze --all-databases -uroot -S /usr/mysql/8.0.36/data/mysql.sock
airportdb.airline OK
airportdb.airplane OK
airportdb.airplane_type OK
airportdb.airport OK
airportdb.airport_geo OK
airportdb.airport_reachable OK
airportdb.booking OK
airportdb.employee OK
airportdb.flight OK
airportdb.flight_log OK
airportdb.flightschedule OK
airportdb.passenger OK
airportdb.passengerdetails OK
airportdb.weatherdata OK
bench.activity OK
bench.project OK
bench.tag_ticket OK
bench.team OK
bench.team_user OK
bench.ticket OK
bench.user OK
d1.t1 OK
d1.t2 OK
d2.child OK
d2.parent OK
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK
mysql.engine_cost OK
mysql.func OK
mysql.general_log
note : The storage engine for the table doesn't support analyze
mysql.global_grants OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.password_history OK
mysql.plugin OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.replication_asynchronous_connection_failover OK
mysql.replication_asynchronous_connection_failover_managed OK
mysql.replication_group_configuration_version OK
mysql.replication_group_member_actions OK
mysql.role_edges OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log
note : The storage engine for the table doesn't support analyze
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
sbtest.sbtest1 OK
sys.sys_config OK
world.city OK
world.country OK
world.countrylanguage OK
折角なので一応MySQL Workbenchで出させたER図も置いておく。
これだけ見ても、どんなクエリがどんな日本語に
0 件のコメント :
コメントを投稿