2018-10-29

MySQL8 SQL Window関数とCTE表でデータクレンジングと名寄せを実現する

とあるデータのクレンジング、名寄せ作業をSQLだけで完結する上で、よくわからなくなってしまったので、メモします。

例えば、こんな定義とデータがあったとして。

定義

CREATE TABLE `artist_master` (
  `Id` bigint(20) unsigned NOT NULL,
  `CreatedAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `Valid` tinyint(4) NOT NULL DEFAULT '1',
  `Name` varchar(128) NOT NULL,
  `VideoCount` bigint(20) unsigned NOT NULL DEFAULT '0',
  `Ext` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
;

データ

mysql> SELECT * FROM artist_master ;
-----------------
+-----------------+---------------------+-------+----------------+------------+--------------------------------------+
| Id              | CreatedAt           | Valid | Name           | VideoCount | Ext                                  |
+-----------------+---------------------+-------+----------------+------------+--------------------------------------+
|  38095832004676 | 2018-10-26 06:50:23 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 333655795382791 | 2018-10-26 06:50:21 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 354380886937504 | 2018-10-26 06:50:25 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 391755868626561 | 2018-10-26 06:50:24 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 504480813693733 | 2018-10-26 06:50:22 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 577030850315619 | 2018-10-26 06:50:24 |     1 | Chris Alan Lee |          0 | https://www.imdb.com/name/nm1327414/ |
| 629430777071848 | 2018-10-26 06:50:20 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 643255722139392 | 2018-10-26 06:50:20 |     1 | Chris Alan Lee |          0 | NULL                                 |
+-----------------+---------------------+-------+----------------+------------+--------------------------------------+

やりたいこと

上記のデータを踏まえ、

577030850315619のValid値を1のまま保守して、その他のValid値を0にしたい場合、どうすればいいんだっけ？というものを考えてまして・・・いわゆる、データークレンジングとか名寄せの話になるのですが（汗）それをSQLのみで実現するにはどうしたらいいってお話です

2018-10-26

MySQL LIKE JOIN というアウトローなクエリーを試してみる

MySQL LIKE JOIN JOIN UPDATE 部分一致 SQL クエリー concat

こんなの不可能だろと思いつつやってみたら以外と出来たというクエリーについて紹介します。

JOIN のON句にLIKEを使うというだけなのですが、これが以外と使える場面があります。今回、音楽のアーティスト名周りでこの難解なSQLをどのように活用するかを考えてみました。

2018-10-26

プログラムは３日で忘れる私が再びpython3メモ pymysql

python3 pymysql select insert 配列のループ

プログラムはちょっとやらないと忘れる。数ヶ月前にやったはずなのに、いざ再開すると、同じところでつまづく。今回は MySQL接続で利用するpymysqlでのメモをします。エラートラップとか例外とかは、書いてません。ちゃんとかける方はこのページなんかみないと思いますので簡単に書きました。

そして、これも書いたことを忘れるんだろうなきっと。

2018-10-11

python3 ディクショナリ型の要素アクセスで、階層がある要素のkeyerrorを回避する方法

python3 ディクショナリ辞書 json ネスト階層 get関数 key error keyerror

例えば、下記のようなネスト（階層）があるディクショナリ型の一部要素を参照しようとしていたのですが

(略）
    "datePublished": "2018-10-11",
    "byArtist": {
        "@type": "MusicGroup",
        "url": "https://yyyyy.zzzzzz/us/artist/abcdefg-gambino/466842536",
        "name": "Childish Gambino"
    }
}

このnameの要素にアクセスする場合、

_Artist = data['byArtist']['name']

と書いていたのですが、ものによって、このname自体がそもそもないものもありまして、KeyErrorが発生し、処理が途中でこけてしまうことがあったのですが、get関数であれば回避できますよ！って、こちらを参考にさせていただき（リンク失礼します）

tomoprog.hatenablog.com

ふむ、なるほどなるほどと思いつつ、あれ？階層（ネスト）がある場合、getでどう表現すればいいんだっけ？というのがわからず、それを回避した（厳密に言うと、詳しい人に聞いた）方法をメモします

前置き長くてすみません

2018-09-21

route53 ドメイン移管とDNS変更は意味違うよって話（お名前comから、route53にDNS変更をする）

aws route53 お名前ドメイン移管 DNS移管手順ゾーン変更 DNS DNSレコード nslookup dig ドメインnavi お名前com

f:id:hit10231023:20180921202021p:plain

たまにしかやらない DNS変更作業、いざやろうとすると、あれ？どうするんだっけ？ってなりませんか？少なくとも私はそうなりました。今回、お名前comでドメインを登録したのですが、DNSは、aws route53のほうが管理しやすいので、切り替えようと考えました。

そして、調べて実施したのが、

お名前comからroute53のドメイン移管（ドメインレジストラ移管）

でした。完全に舵を取り違えた感じです（汗）

そしてそもそもの認識違いの発端。。。

ご指摘通り、DNS移管という、謎キーワードを、脳内で作成しまったからであります。

DNS移管ではなく、DNS変更です（汗）

この間違えを踏まえ、メモがわりに記事を掲載しようと思った次第でありまして。。

内容は、お名前comのDNSをroute53に切り替えた話です

2018-09-18

いまさらだけど、ltsvが超便利！

log apache nginx LTSV TSV CSV ラベルアクセスログ

f:id:hit10231023:20180918181949p:plain

はじめltsvってapacheのモジュールかと思ってましたら・・そうじゃなくって、単純にLogFormatをラベル付きのログを出力する形式ってだけなんです。でも、また、これが便利、たとえば、access.log等は、標準ではラベルが入っていないため、大量にある場合、若干みづらいですよね。これがltsvであれば、ぐっと見やすくなります。

ltsv.org

実際に試して見ましたが、ltsvってとっても便利！っていう話と、例として、apacheでの設定を中心で、おまけでnginxではどのように設定するのかなんていうのを書いていきます

2018-09-18

MySQL HyperDB + Keepalivedを利用した、wordpress の負荷分散

MySQL wordpress plugin 負荷分散ロードバランシング keepalived

当記事、2012年に書いた記事になりますので、hyperdbがあるかないのかもわかりませんが、当方で活用する案件が発生しましたので、再アップしました。

思っていたより簡単にできた、そして結果からいいますと、思っていたよりパフォーマンスがよく無かった記憶があるのですが、こちらで検証したチープな環境だったからでしょう。。。きっと

表題の通り、wordpressのデータベースを、MySQL レプリケーションを駆使して負荷分散する際に活用できるプラグインです。それプラスといってはなんですが、KeepAlived(LVS）を間にはさんで、スケールアウトしやすい構成にしていました、以下の構成図になります。

2018-09-17

MySQLでいいね！（LIKE）機能のDB設計をしてみた！

SQL MariaDB twitter いいねソーシャル DB設計テーブル設計 LIKE SEO

f:id:hit10231023:20160517120005j:plain

いいねってなに？

FACEBOOKですと、いいね、 Twitterだと♥のあれです。エンゲージメント率を高めるばかりではなく、記事の拡散にも効果を発揮しますよね。また、サービス運営者視点で話すと、話題が見つけやすくなりますよね。ってなわけで、今度はいいね。のテーブル設計をどうするか？というのは試してみたいと思います

2018-09-16

mysql 文字列結合のconcat関数は||(パイプ）でスマートに書こう！

mysql concat pipe mariadb sql 文字列結合 SQL PIPES_AS_CONCAT

MySQLおよび、MariaDBでの文字列結合関数といえばCONCAT（コンキャット）です。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5 文字列関数

文字列結合にいちいちconcatなんていう関数を使うのはめんどくさい、且つわけがわからなくなるので、パイプ(||)ショートカットでconcatと同様の操作がおこなえるように変更するtipsです

ビッグデータ分析・活用のためのSQLレシピ

2018-09-16

MySQL SQLとbashヒアドキュメントをうまく使いこなそう！！

bash ヒアドキュメント MySQL 変数 shell シェルヒヤドキュメント

MySQLとヒアドキュメントをうまく使いこなされば、さくっとSQLをファイルを作ることができます。はじめは戸惑いますが慣れれば簡単です。数百回はやったかも（＾＾;）ヒアドキュメント（heredocument)自体は、MySQLとはまったく関係のない話、そもそもbashではありますが、MySQLとbashヒアドキュメントの相性は大変良いので、この機会に覚えておいたほうが良いかもしれません。また、ヒアドキュメントを有効に活用できるようになれば、プログラムを作らなくても、シェルスクリプトだけで、バッチ処理を作ることも可能になり、なんにせよ、高速です！（プログラムが遅いっていっているわけではありませんのでそのは誤解しないでください）

2018-09-12

ec2 rootから特定のユーザーに成り代わってシェルやコマンドを実行する（runuser)

ユーザー成り代わり切り替え aws ec2 ec2-user root runuser 特定のユーザー linux centos amazon linux

f:id:hit10231023:20180912204416p:plain

意外と知らなかったです。 root以外のアカウントから、rootに成り代わって実行はいくつかあるんですが、 rootからroot以外のアカウントに成り代わって実行する方法ってどうするんだろう？って

例えばこんなケースです

[root@]# aws ec2 describe-instance-status
You must specify a region. You can also configure your region by running "aws configure".

まあ、当然そうなりますよね。aws configureは、ec2-userで設定しているわけなんで笑

このような場合、いままでsudoコマンド特定のユーザーに成り代わって実行していたのですが、amazon linuxおよびcentos等の起動コマンド・起動シェル内では設定しても動いてくれない（汗）

コンソールから直接叩くと動くんだけどな。。

[root@]# sudo -H -u  ec2-user aws ec2 describe-instance-status

調べた限りでは、

RedHat系のデフォルト設定では起動時のsudoは許されていないってのが理由らしく、じゃーどうすればいいの？って調べたところ runuserで実行できることがわかりました。

2018-09-11

MySQL 結局、索引の最大長っていくつなの？を考える Specified key was too long; max key length is 3072 bytes

MySQL MySQL8 インデックス INDEX インデクス最大長バイト文字数文字コード utf8 utf8mb3 utf8mb4 キー分割 Byte

f:id:hit10231023:20180911165611p:plain

MySQL8.0の話ですので、以前のバージョンの話とはちょっと変わってしまうかもしれませんが、テーブルの作成で、ものすごくしょうもないことでハマってしまいました。

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

まず、前提として、索引（インデックス）の最大長は、3072バイトです。ってところから始めます（笑）

これがいけると思っていたところからが地獄の始まり（笑）都合よく、私は、varchar(1024)を1024バイトだと思ってました（汗）

CREATE TABLE `table1` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Url` varchar(1024) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `ix01` (`Url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
;
----------------------------------------
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes