動機 Spark2.1 + Zeppelinで解析基盤を構築してます。UnionByNameとかが使えないので、Spark3.0にアップデートすることにしました。 環境 Apache Hadoop 2.7.3 Apache Spark 2.1 Docker mySQL 5.7.3 (metastore) 課題 ダウンロードして docker start mysqld ${HADOOP_HOME}/sbin/start-dfs.sh ${SPARK_HOME}/sbin/start-all.sh したら、 spark hive Caused by: org.datanucleus.exceptions.NucleusException: Attempt to invoke the "BONECP" plugin to create a ConnectionPool gave an error : The specified datastore driver ("com.mysql.jdbc.Driver") was not found in the CLASSPATH 解決 色々調べてとりあえずhive.jarのバージョンがあっていない様子。 https://support.datafabric.hpe.com/s/article/Spark-Hive-Integration-failure?language=ja hive-site.xmlに <name>hive.metastore.schema.verification</name> <value>false</value> を記載して対処し、とりあえず無事動きました。 <configuration> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://XXXX/metastore_db?createDatabaseIfNotExist=true</value> <description>metastore</description> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> <description>MySQL JDBC driver class</description> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>XXXX</value> <description>user name for connecting to mysql server </description> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>XXXX</value> <description>password for connecting to mysql server </description> </property> <property> <name>hive.metastore.schema.verification</name> <value>false</value> </property> <property> <name>hive.metastore.warehouse.dir</name> <value>hdfs://XXXX</value> <description>location of default database for the warehouse</description> </property> </configuration>

背景 php学習中ですがphpmyadminにmysqlのパスワードがわからずアクセスできなかった。 解決するまでに苦戦してしまったので忘れないように。 解決方法 my.iniファイルを開き、[mysqld]の欄にskip-grant-tablesを追記　(C:\xampp\mysql\bin\my.ini) Mysqlを再起動 コマンドプロンプトからmysqlにアクセス(mysql -u root -p) flush privileges;　エラーが返ってくるが無視 ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';でパスワードを再設定 my.iniの[mysqld]欄skip-grant-tablesを削除し、mysqlを再起動 mysql -u root -pで再設定したパスワードでログインできるか確認する。 config.inc.phpのパスワードも今回再設定したものに書き換えた結果、 phpmyadminにアクセスできるようになりました。

MySQL 8.0.24 では相関スカラーサブクエリ（correlated scalar subquery）の最適化が新たに導入されたと聞いて、試してみました。 効果が確認できずに一旦記事化を断念したのですが、こちらの記事でオプティマイザスイッチの指定が必要だったことを知り（ありがとうございます）、再確認してみました。 今日は、MySQL8.0.24の変更点オプティマイザーノートについての日。（今日はなにの日。） ※一旦断念する前は、うっかりヒント句ばかり試していました。 相関スカラーサブクエリとは？ 相関サブクエリはサブクエリの評価を（最初に 1 回ではなく）複数回行う必要があるサブクエリ、スカラーサブクエリは最大で 1 行を返すサブクエリですが、これだけだとよくわからないので以下の記事を参考にしてください。 SQLアタマアカデミー（gihyo.jp） 第9回　SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～　（2）相関サブクエリ 第9回　SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～　（3）便利で危険な相関サブクエリ① 第9回　SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～　（4）便利で危険な相関サブクエリ② MySQL 8.0 の公式リファレンスマニュアルの説明ページはこちらです（「Beginning with MySQL 8.0.24」以降の部分）。 13.2.11.7 Correlated Subqueries（MySQL 8.0 Reference Manual） ※なお、MySQL 8.0 公式リファレンスマニュアルには日本語版が登場しましたが、残念ながらこの項目の記載はありません。 MySQL 8.0 日本語リファレンスマニュアルが公開されました（note） 試してみる 公式リファレンスマニュアルに示されている SQL（文）に近いものを試してみます。 テーブル定義 テーブル定義 mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, `a` int NOT NULL, `str` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=163841 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) mysql> SHOW CREATE TABLE t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int NOT NULL AUTO_INCREMENT, `a` int NOT NULL, PRIMARY KEY (`id`), KEY `idx_a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=65537 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) テストデータ t1 が 10 行 1 セット、t2 が 4 行 1 セットでそれぞれ 163,840 行、65,536 行生成しました（同じパターンの繰り返し）。 テストデータ mysql> SELECT * FROM t1 ORDER BY id LIMIT 10; +----+----+----------------------------------------------------+ | id | a | str | +----+----+----------------------------------------------------+ | 1 | 1 | 1111111111 | | 2 | 2 | 22222222222222222222 | | 3 | 3 | 333333333333333333333333333333 | | 4 | 4 | 4444444444444444444444444444444444444444 | | 5 | 5 | 55555555555555555555555555555555555555555555555555 | | 6 | 6 | 6666666666666666666666666666666666666666 | | 7 | 7 | 777777777777777777777777777777 | | 8 | 8 | 88888888888888888888 | | 9 | 9 | 9999999999 | | 10 | 10 | 10 | +----+----+----------------------------------------------------+ 10 rows in set (0.00 sec) mysql> SELECT * FROM t1 ORDER BY id LIMIT 163830, 10; +--------+--------+----------------------------------------------------+ | id | a | str | +--------+--------+----------------------------------------------------+ | 163831 | 163831 | 1111111111 | | 163832 | 163832 | 22222222222222222222 | | 163833 | 163833 | 333333333333333333333333333333 | | 163834 | 163834 | 4444444444444444444444444444444444444444 | | 163835 | 163835 | 55555555555555555555555555555555555555555555555555 | | 163836 | 163836 | 6666666666666666666666666666666666666666 | | 163837 | 163837 | 777777777777777777777777777777 | | 163838 | 163838 | 88888888888888888888 | | 163839 | 163839 | 9999999999 | | 163840 | 163840 | 10 | +--------+--------+----------------------------------------------------+ 10 rows in set (0.11 sec) mysql> SELECT * FROM t2 ORDER BY id LIMIT 4; +----+---+ | id | a | +----+---+ | 1 | 1 | | 2 | 3 | | 3 | 7 | | 4 | 8 | +----+---+ 4 rows in set (0.00 sec) mysql> SELECT * FROM t2 ORDER BY id LIMIT 65532, 10; +-------+--------+ | id | a | +-------+--------+ | 65533 | 163831 | | 65534 | 163833 | | 65535 | 163837 | | 65536 | 163838 | +-------+--------+ 4 rows in set (0.02 sec) EXPLAINの確認 最適化 OFF の場合 EXPLAIN(最適化OFF) mysql> EXPLAIN FORMAT=TREE -> SELECT * FROM t1 -> WHERE ( SELECT a FROM t2 -> WHERE t2.a=t1.a ) > 0\G *************************** 1. row *************************** EXPLAIN: -> Filter: ((select #2) > 0) (cost=16682.58 rows=163618) -> Table scan on t1 (cost=16682.58 rows=163618) -> Select #2 (subquery in condition; dependent) -> Index lookup on t2 using idx_a (a=t1.a) (cost=1.09 rows=1) 1 row in set, 1 warning (0.00 sec) Visual Explain だとこんな感じです。 ※コスト試算値など数値が違う部分がありますが、サンプリング統計情報をベースとした試算値なので実行タイミングで変わることがあります。以降も同様。 最適化 ON の場合 EXPLAIN(最適化ON) mysql> EXPLAIN FORMAT=TREE -> SELECT /*+ SET_VAR(optimizer_switch = 'subquery_to_derived=on') */ * FROM t1 -> WHERE ( SELECT a FROM t2 -> WHERE t2.a=t1.a ) > 0\G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=10761.64 rows=10981) -> Filter: (reject_if((derived_1_2.Name_exp_2 > 1)) and (derived_1_2.a is not null)) (cost=0.34..3709.04 rows=10981) -> Table scan on derived_1_2 (cost=2.50..2.50 rows=0) -> Materialize (cost=2.50..2.50 rows=0) -> Group aggregate: count(0) -> Filter: (t2.a > 0) (cost=6622.10 rows=32947) -> Index range scan on t2 using idx_a (cost=6622.10 rows=32947) -> Index lookup on t1 using idx_a (a=derived_1_2.a) (cost=0.54 rows=1) 1 row in set, 1 warning (0.01 sec) Visual Explain だとこんな感じです。 複雑化していますが、コスト試算値は最適化 ON で小さくなっているようです。 実行時間を比較してみる 元のままの SQL（文） SELECT *をSELECT COUNT(*)に変えたもの の 2 種類で試した結果です（単位 : 秒）。 相関スカラーサブクエリ最適化 OFF ON SELECT * 0.39 0.17 SELECT COUNT(*) 0.62 0.89 元のままのSELECT *では遅くなり、SELECT COUNT(*)では逆に速くなるという微妙な結果になりました。 EXPLAINで表示される結果に差はないのですが、件数だけ返すのとデータページの列も返すのとでは実質的な処理の量が変わるようです。 余談 この記事は MySQL 8.0 の薄い本 8.0.24 対応版改訂に向けて書いたものですが、すでに予告したとおり、MySQL 8.0 の薄い本は 5 月発行予定の 8.0.24 対応版で更新作業を終了します。 「MySQL 8.0 の薄い本」更新終了のお知らせ（note） 更新期間は 2 年余りでしたが、お付き合いいただいたみなさま、ありがとうございました。 今後は MySQL 関連のコミュニティ活動から少し距離を置きます（ちょっと別のことを始める準備期間が必要なので。昔風にいえば「ROM ります」的なスタンスです）。 登壇を含むアウトプット活動は MySQL 8.0 の薄い本 8.0.24 対応版で一旦終了です（ヒラメじゃなくなったイルカさん、欲しかったけどタイミングが合わなくて残念でした）。 Qiitaに投稿したMySQL 8.0関連記事 MySQL 8.0 の薄い本（無料で配布中！） ←書くのはこれで最後

SQL文チートシート①の続き 目次 CREATE INSERT SELECT ALTER UPDATE DELETE 　使用テーブル moviesテーブルスキーマ name type id INTEGER name TEXT genre TEXT year INTEGER rating INTEGER moviesテーブルデータ id name genre 　year rating 1 Into The Wild adventure 2007 9 2 Point Break action 1991 5 3 Inception SF 2010 3 4 Pulp Fiction comedy 1994 7 5 Se7en action 1992 10 6 Seven action 1999 8 AS AS文はカラムやテーブル名をエイリアスとして変更することができます。さっそくカラム名を変更してみます。 movies.sql SELECT name AS 'Titles' FROM movies; SQL結果 Titles Into The Wild Point Break Inception Pulp Fiction Se7en Seven シングルクォートではなくても大丈夫ですが、ASを使用する際は慣習的にシングルクォートを使います。また、ASはテーブルのカラムの名前を実際には変更しておらず、結果として仮に別の名前で表示しているだけです。 DISTINCT DISTINCT文は指定したカラムでダブっている値をフィルタリングします。 moives.sql SELECT DISTINCT genre FROM movies; SQL結果 genre adventure action SF comedy ダブっていたcomedyがフィルタリングされました。 WHERE WHERE句は指定した範囲の情報を取得することができ、以下の様なさまざまな比較演算子が使われます。 A = B（A と B が等しい） A <> B or A != B（A と B が等しくない。） A > B（A より B が小さい） A < B（A より B が大きい） A >= B（A が B 以上） A <= B（A が B 以下） moives.sql SELECT * FROM movies WHERE rating <= 7; SQL結果 id name genre 　year rating 2 Point Break action 1991 5 3 Inception SF 2010 3 4 Pulp Fiction comedy 1994 7 Like LIKEはWHERE句とセットで使用し、カラム内のある特定のパターンにマッチした値を取得します。その際に使用できるワイルドカードが以下の2つです。 _ % 「 _ 」の使い方 _は一つの文字を表します。 moives.sql SELECT * FROM movies WHERE name LIKE 'Se_en'; SQL結果 id name genre 　year rating 5 Se7en action 1992 10 6 Seven action 1999 8 _が一文字分のワイルドカードとして機能し、Seから始まりenで終わる値にマッチし取得することができます。 「 % 」の使い方 %は0個かそれ以上の数の文字を表します。 moives.sql SELECT * FROM movies WHERE name LIKE 'I%'; SQL結果 id name genre 　year rating 1 Into The Wild adventure 2007 9 3 Inception SF 2010 3 I%は映画の名前がIから始まる全ての映画にマッチします。 BETWEEN BETWEENはWHERE句の中で使われ、「数字」「文字」「日付」の値の範囲を指定しフィルタリングすることができます。 moives.sql SELECT * FROM movies WHERE year BETWEEN 1990 AND 1999; SQL結果 id name genre 　year rating 2 Point Break action 1991 5 4 Pulp Fiction comedy 1994 7 5 Se7en action 1992 10 6 Seven action 1999 8 WHERE句内での条件の組み合わせ WHERE句内で複数の条件で絞り込む場合以下の２つを使用します。 AND OR AND ANDはすべての条件がtrueの行を表示します。 moives.sql SELECT * FROM movies WHERE year BETWEEN 1990 AND 1999 AND genre = 'action'; SQL結果 id name genre 　year rating 2 Point Break action 1991 5 OR ORは条件が一つでもtrueであれば、その行を表示します。 moives.sql SELECT * FROM movies WHERE year BETWEEN 1990 AND 1999 OR genre = 'SF'; SQL結果 id name genre 　year rating 2 Point Break action 1991 5 3 Inception SF 2010 3 4 Pulp Fiction comedy 1994 7 5 Se7en action 1992 10 6 Seven action 1999 8 ORDER BY ORDER BYはSQLの結果を数字もしくは文字で任意の順番にソートすることができます。 ASC = 昇順 DESC = 降順 で指定することが可能です。 moives.sql SELECT * FROM movies WHERE rating >= 8 ORDER BY year DESC; SQL結果 id name genre 　year rating 1 Into The Wild adventure 2007 9 6 Seven action 1999 8 5 Se7en action 1992 10 ratingが8以上の行を公開年が新しいもの順に並べ替えました。 LIMIT LIMITは取得するデータの行数の上限を設定することができます moives.sql SELECT * FROM movies LIMIT 3; SQL結果 id name genre 　year rating 1 Into The Wild adventure 2007 9 2 Point Break action 1991 5 3 Inception SF 2010 3 補足: LIMITは必ずクエリ最後に書きます。また、LIMITをサポートしてないSQLデータベースがあります。 CASE CASEはWHENで指定した条件を上から順番にチェックしていき、最初にtrueとなる条件の結果を返します。 もしいずれにも合致せず、ELSEが指定されている場合はELSEの結果を返します。 また、どれも合致せず、ELSEが指定されていない場合はNULLを返します。 moives.sql SELECT name, CASE WHEN rating >= 9 THEN 'Awesome' WHEN rating >= 5 THEN 'Good' ELSE 'Bad' END AS 'Review' FROM movies; SQL結果 name Review Into The Wild Awesome Point Break Good Inception Bad Pulp Fiction Good Se7en Awesome Seven Good

問題 Docker-compose up だとコンテナは起動して、正常にMysqlが動作する。 VSCodeの Remote containers: Reopen in container で開くとコンテナは起動しているが、Mysqlが動作していない。 原因と解決策 プロジェクトのrootディレクトリにdocker-compose.ymlが存在する状態で、 Remote containers: Reopen in containerを実行すると、既存のdocker-compose.ymlを読み込む、設定が、.devcontainer/に作成され、Docker-compose upが実行されます。 VSCodeが自動的に作成する、.devcontainer/docker-compose.yml のcommand:の行が悪影響してMysqlが起動していませんでした。 command: の行をコメントアウトすると解決します。 なにか意味があって、command:の行を生成しているのだろうけど、必要性が全く不明。 version: '3' services: # Update this to the name of the service you want to work with in your docker-compose.yml file db: # If you want add a non-root user to your Dockerfile, you can use the "remoteUser" # property in devcontainer.json to cause VS Code its sub-processes (terminals, tasks, # debugging) to execute as the user. Uncomment the next line if you want the entire # container to run as this user instead. Note that, on Linux, you may need to # ensure the UID and GID of the container user you create matches your local user. # See https://aka.ms/vscode-remote/containers/non-root for details. # # user: vscode # Uncomment if you want to override the service's Dockerfile to one in the .devcontainer # folder. Note that the path of the Dockerfile and context is relative to the *primary* # docker-compose.yml file (the first in the devcontainer.json "dockerComposeFile" # array). The sample below assumes your primary file is in the root of your project. # # build: # context: . # dockerfile: .devcontainer/Dockerfile volumes: # Update this to wherever you want VS Code to mount the folder of your project - .:/workspace:cached # Uncomment the next line to use Docker from inside the container. See https://aka.ms/vscode-remote/samples/docker-from-docker-compose for details. # - /var/run/docker.sock:/var/run/docker.sock # Uncomment the next four lines if you will use a ptrace-based debugger like C++, Go, and Rust. # cap_add: # - SYS_PTRACE # security_opt: # - seccomp:unconfined # Overrides default command so things don't shut down after the process ends. command: /bin/sh -c "while sleep 1000; do :; done" ひとりごと Remote containers使っているほかの人の記事では特に問題になってないのに、なぜ？？ 解決するのに半日かかりました。 いろいろ調査している過程で、Remote containersの理解が深まりました。

主キーを変更する方法をよく忘れてしまうので、自分用のメモとして残します。 テスト用テーブル CREATE TABLE employee( id int NOT NULL AUTO_INCREMENT ,code varchar(45) NOT NULL ,name varchar(45) ,age int ,PRIMARY KEY(id) ,UNIQUE(code) ); +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | code | varchar(45) | NO | UNI | NULL | | | name | varchar(45) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ PRIMARY KEY制約とUNIQUE制約を変更 ALTER TABLE employee DROP PRIMARY KEY ,DROP INDEX code ,ADD PRIMARY KEY (code) ,ADD UNIQUE (id) ; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | UNI | NULL | auto_increment | | code | varchar(45) | NO | PRI | NULL | | | name | varchar(45) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+