Database - シドニーで働くプログラマーのBlog(今はIT会社の経営者)

AWS – Building a Modern Data Architecture – Sydneyに行ってきました。

Mar 10, 2017

AD:

先日AWSのBuilding a Modern Data Architectureのイベントに行ってきたのでそのメモです。
イベントはどちらかというとDevOpsやBIの人たち向けでしたが、大変勉強になりました。
AWSのサービスが多すぎて正直どんなサービスがあって、どのように使えばいいのかわからなかったのですが、具体的にグラフを元に説明してくれたので勉強になりました。

AWSでモダンなBigDataを扱う構成のグラフです。

各種サービスはこちらになります。

Amazon QuickSight – BI tools for AWS DB service – tableau, and more dashboard tools
Amazon Athena – Interactive query service that makes it easy to analyze data in Amazon S3 using standard S
Amazon Kinesis – Collect and process large streams of data records in real time
Amazon Lambda – event-driven, serverless computing platform
Amazon Polly – text to real voice
Amazon Lex – Build Conversation Bots,
Amazon Rekognition- Image recognition, place & face
Amazon DynamoDB – Fast and flexible NoSQL database service for any scale
Amazon Simple Queue Service (SQS) – Simple Queue system

もしBigDataやAWSのサービスをもっと知りたい場合はこちらを利用したらって
https://aws.amazon.com/podcasts/aws-techchat-subscribe/
https://aws.amazon.com/jp/blogs/big-data/

Twitter:
Warning: Undefined array key "Twitter" in /home/sazaeau/mizoshiri.com/public_html/blog.mizoshiri.com/wp-content/plugins/sns-count-cache/sns-count-cache.php on line 2897
0 | Facebook: 0 | Google Plus:
Warning: Undefined array key "Google+" in /home/sazaeau/mizoshiri.com/public_html/blog.mizoshiri.com/wp-content/plugins/sns-count-cache/sns-count-cache.php on line 2897
0 | Hatena: 0 | Pocket: 0 | Total: 0 | Feedly: 0

WordPressでユーザ情報(wp_users+wp_usermetaからfirst_name,last_name)をSQLで一度に取ってくるSQL

Oct 14, 2015

AD:

WordPressでユーザの情報を名前や、そのほかのもろもろの情報をCSVで出力したかったのですが、そのSQLをメモしておきます。
wp_userテーブルに基本、wp_usemetaに姓、名などの細かい情報が入っているのですが下記のようなテーブル構成になっているので、各種項目を一度に取ってくるには一つ一つをjoinして取ってくるしかないので、メモしておきます。

SQLはこんな感じです。

SELECT
    u.ID,
    u.user_email,
    m1.meta_value AS firstname,
    m2.meta_value AS lastname,
    m3.meta_value AS country
FROM wp_users u
JOIN wp_usermeta m1 ON (m1.user_id = u.ID AND m1.meta_key = 'first_name')
JOIN wp_usermeta m2 ON (m2.user_id = u.ID AND m2.meta_key = 'last_name')
JOIN wp_usermeta m3 ON (m3.user_id = u.ID AND m3.meta_key = 'country')
Limit 100;

1

2

3

4

5

6

7

8

9

10

11

SELECT

u.ID,

u.user_email,

m1.meta_value AS firstname,

m2.meta_value AS lastname,

m3.meta_value AS country

FROM wp_users u

JOIN wp_usermeta m1 ON (m1.user_id = u.ID AND m1.meta_key = 'first_name')

JOIN wp_usermeta m2 ON (m2.user_id = u.ID AND m2.meta_key = 'last_name')

JOIN wp_usermeta m3 ON (m3.user_id = u.ID AND m3.meta_key = 'country')

Limit 100;

もしSQLから直接CSVを書きだしたかったら、参考まにでこちらをどうぞ
MysqlでSelectの結果をCSVにエクスポートする

Twitter:
Warning: Undefined array key "Twitter" in /home/sazaeau/mizoshiri.com/public_html/blog.mizoshiri.com/wp-content/plugins/sns-count-cache/sns-count-cache.php on line 2897
0 | Facebook: 0 | Google Plus:
Warning: Undefined array key "Google+" in /home/sazaeau/mizoshiri.com/public_html/blog.mizoshiri.com/wp-content/plugins/sns-count-cache/sns-count-cache.php on line 2897
0 | Hatena: 0 | Pocket: 2 | Total: 2 | Feedly: 0

PostgreSQL dumpしたものをHerokuのAppにインポート(push)する方法

Sep 5, 2015

AD:

更新 – 20170704

heroku pg:backups restore 'https://dl.dropboxusercontent.com/u/??/dumpfile.dump' DATABASE_URL --app 対象のAPP名

1	heroku pg:backups restore 'https://dl.dropboxusercontent.com/u/??/dumpfile.dump' DATABASE_URL --app 対象のAPP名

DATABASE_URL は下記のように取得しました。
Heroku PostgresのDatabase Credentialsをクリックして、Heroku CLIのpostgresql-????-????をつかいました。

更新 – 2016/09/18

heroku pg:backups restore 'https://dl.dropboxusercontent.com/u/??/dumpfile.dump' DATABASE_URL --app 対象のAPP名

1	heroku pg:backups restore 'https://dl.dropboxusercontent.com/u/??/dumpfile.dump' DATABASE_URL --app 対象のAPP名

実際にやりたかったことは、ProductionのAPPからStagingのAppにPostgreSQLのDumpをインポートしたかったのですが、HerokuがBackupの方法が変わったので、以前と変わっていたのでメモしておきます。
まず、ProductionのPostgreSQLをDumpしてきます。
その後、それをローカルのPostgreSQLにImportします。

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U ユーザ名 -d データベース名 ダンプファイル.dump

1	pg_restore --verbose --clean --no-acl --no-owner -h localhost -U ユーザ名 -d データベース名ダンプファイル.dump

次はそれをpg:pushを使ってHerokuのPostgreSQLにインポートします。

heroku pg:push ローカルのデータベース名 対象のAPPのDBURL(HEROKU_POSTGRESQL_色) --app 対象のAPP名

1	heroku pg:push ローカルのデータベース名対象のAPPのDBURL(HEROKU_POSTGRESQL_色) --app 対象のAPP名

MacでPostgreSQLのクライアントならPosticoがかなりおすすめ

Aug 26, 2015

AD:

今までpgAdmin3を使っていたのですが、これが遅い、落ちるたりするのでなかなか使い勝手がよくなかったのですが、先日Posticoなるものを発見して使い出しましたが、かなり気にいっております。

SSH経由での接続ももちろん可能です
Vagrantの中にあるPostgresなんかにもこれで接続できます

接続後の画面

テーブル内のデータ編集画面

SQLの画面

有料版はFilter機能もあります

詳細

サポートしてPostgreSQLのバージョン
PostgreSQL 8.0, 8.4, 9.1, 9.2, 9.3 9.4、Amazon Redshiftもサポートしているようです。
Posticoはlibpq 9.3.8のパッチをつかっているそうです。
トライアル版と有料版の違い
トライアル版は、お気に入りに5 connectionまでした登録できません。
トライアル版は、１画面でしかしようできなくなっています。
テーブル内の検索機能がありません。（）
トライアル版には、期間制限がないので、ずっと使い続けることも可能です。

正直無料版でもよかったのですが、開発者に敬意を示して、ちゃんと有料版を購入しました。

Version Up

Postico v1.0.9 – 2016/08/05
Command + pのQuick Openなるものが追加されていますが、すぐにテーブルに飛べるのでかなり便利です。
Screen Shot 2016-08-06 at 12.43.22 PM

RailsのTaskでデータをインサートしたらDuplicate key value violates unique constraintがでる場合

Jul 24, 2015

AD:

RailsのTaskでレポートを生成するように作っているのですが、スケジュールで毎週動かすようにしていたのですが、どうもうまくいっていないようなので、調べてみたら、下記のエラーになっていました。
プライマーキーの最大値が更新されていないようです。

rake aborted!
PG::Error: ERROR:  duplicate key value violates unique constraint "reports_pkey"
DETAIL:  Key (id)=(2) already exists.

1

2

3

rake aborted!

PG::Error: ERROR: duplicate key value violates unique constraint "reports_pkey"

DETAIL: Key (id)=(2) already exists.

対処方法

Railsが独自のPostgreSQLシーケンスをつかっているかららしいです。
なので、SQLで最大値をセットしてあげます。これで問題なく動くようになりました。

SELECT setval('reports_id_seq', (SELECT max(id) FROM reports));

1	SELECT setval('reports_id_seq', (SELECT max(id) FROM reports));

Drupalのモジュールを管理しているテーブル

Jul 14, 2015

AD:

先日DrupalのあるモジュールがFunctionのredeclareエラー（同じFunctionを２回呼んでいるよー）がで困った際に調査してわかりましたので、メモしておきます。
調べてみるとたしかにフォルダ別に同じモジュールがあることがわかりました。
でも、どちらのモジュールを使用しているか、Adminページではわからなかったて、同僚に聞いてみるとsystemテーブルなるものがあるようです。

systemテーブルに下記のようにモジュールの種類や、Weight、ファイルのパスなどを確認できるようになっております。

Twitter:
Warning: Undefined array key "Twitter" in /home/sazaeau/mizoshiri.com/public_html/blog.mizoshiri.com/wp-content/plugins/sns-count-cache/sns-count-cache.php on line 2897
0 | Facebook: 0 | Google Plus:
Warning: Undefined array key "Google+" in /home/sazaeau/mizoshiri.com/public_html/blog.mizoshiri.com/wp-content/plugins/sns-count-cache/sns-count-cache.php on line 2897
0 | Hatena: 1 | Pocket: 0 | Total: 1 | Feedly: 0

cannot drop the currently open database – Postgres drop datababaseのエラー

Mar 24, 2015

AD:

cannot drop the currently open databaseと出てデータベースが消せませんでしが、dropdbを使用すれば消すことができます。

$ psql
>drop database database_name
cannot drop the currently open database

1

2

3

$ psql

>drop database database_name

cannot drop the currently open database

dropdb

psqlでログインの必要はないです。

dropdb database_name

1	dropdb database_name

Movable Type 5のデータベースからCSV作成してWordPress 4にインポート

Mar 22, 2015

AD:

MovableType 5.2で構築されたブログのデータをWordpress 4にインポートする作業を最近行いましたが、その際にいろいろと面倒だったので、メモを公開させていただきます。
はじめは、MovableTypeのエクスポート機能とWordpressのMTのインポートプラグインを使ってやったところ、文字化けやカテゴリがまったくうまくいきませんでした。

他にもいろいろと手はあると思ったのですが、最終的にMovableTypeのデータベース(Mysql)からCSV作成して、それをWordpressのCSV用のインポートのプラグインを使ってインポートで解決させました。

WordPressのプラグイン

WordPressにCSVのインポート用に下記のプラグインを使用しました。
WP Ultimate CSV Importer Plugin
WP Ultimate CSV Importer Pluginを使って、まず既存のWordpressの記事からCSVのサンプルをエクスポートしました。
それをさらにインポートして試してみました。すると最低限のフィールドがわかったので、下記のフィールドでCSVを作成しました。

MovableTypeのデータベースからWP Ultimate CSV Importer用のCSVを作成

上記のフィールドを元に、下記のSQLを作成して、CSVをエクスポートしました。

SELECT entry_id AS wp_ID, entry_created_on AS wp_post_date,
CASE entry_category_id WHEN NULL THEN 'publish' ELSE 'publish' END AS wp_post_status,
entry_title AS wp_post_title,
REPLACE(entry_text, CHAR(10), '<br>') AS wp_post_content,
entry_basename AS wp_post_name,
CASE entry_category_id WHEN NULL THEN 'ayumi' ELSE 'ayumi' END AS wp_post_author,
mc.category_description AS tx_category
FROM mt_entry AS me
LEFT JOIN mt_placement AS mp ON me.entry_id = mp.placement_entry_id
LEFT JOIN mt_category AS mc ON mc.category_id = mp.placement_category_id
WHERE entry_atom_id LIKE '%column%'
ORDER BY entry_id DESC
INTO OUTFILE '/tmp/mt.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SELECT entry_id AS wp_ID, entry_created_on AS wp_post_date,

CASE entry_category_id WHEN NULL THEN 'publish' ELSE 'publish' END AS wp_post_status,

entry_title AS wp_post_title,

REPLACE(entry_text, CHAR(10), '<br>') AS wp_post_content,

entry_basename AS wp_post_name,

CASE entry_category_id WHEN NULL THEN 'ayumi' ELSE 'ayumi' END AS wp_post_author,

mc.category_description AS tx_category

FROM mt_entry AS me

LEFT JOIN mt_placement AS mp ON me.entry_id = mp.placement_entry_id

LEFT JOIN mt_category AS mc ON mc.category_id = mp.placement_category_id

WHERE entry_atom_id LIKE '%column%'

ORDER BY entry_id DESC

INTO OUTFILE '/tmp/mt.csv'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"';

WP Ultimate CSV Importerは、CSVにヘッダーがないとうまくいかないので、下記のヘッダーを追加してやる

id,date,status,titile,contents,base,user,category

1	id,date,status,titile,contents,base,user,category

インポート

下記の用に管理画面からインポートとどのフィールドにマッチさせるかを選択して、無事インポートができました。

Tips

パーマリンク設定は、/%year%/%monthnum%/%postname%/に変更
MTで内部に使用している画像は同じディレクトリ構成で、WPサイトに設置

MysqlでSelectの結果をCSVにエクスポートする

Mar 10, 2015

AD:

こんな感じで、SELECT分の後にINTO OUTFILEを追加してあげればOKです。

SELECT * FROM user
WHERE status = 1 
ORDER BY id ASC
INTO OUTFILE '/tmp/status_user.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';

1

2

3

4

5

6

SELECT * FROM user

WHERE status = 1

ORDER BY id ASC

INTO OUTFILE '/tmp/status_user.csv'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"';

Twitter:
Warning: Undefined array key "Twitter" in /home/sazaeau/mizoshiri.com/public_html/blog.mizoshiri.com/wp-content/plugins/sns-count-cache/sns-count-cache.php on line 2897
0 | Facebook: 0 | Google Plus:
Warning: Undefined array key "Google+" in /home/sazaeau/mizoshiri.com/public_html/blog.mizoshiri.com/wp-content/plugins/sns-count-cache/sns-count-cache.php on line 2897
0 | Hatena: 0 | Pocket: 1 | Total: 1 | Feedly: 0

MySQL Workbenchで既存データベースからER図を自動に作成

Feb 5, 2015

AD:

結構複雑に入り込んだデータ設計が必要になったので、お客様にもデータを持ち方を確認してもらうためにER図を作成することにしました。
で、MySQL Workbenchに行き着いた感じです。

一部ですが、SQLがすでにあるので、それをいちいち手動でER図書くのは面倒だと思った、ちゃんと既存データベースからER図を作成する方法があるので、メモしておきます。

やり方のメモ

MySQL Workbenchをインストール
データベースの情報を入力する
Continueを押していくと、Mysqlが持っているデータベースの一覧が表示される。
画面はWordpressのDBの一覧です。
どうやらリレーションシップは出してくれないようですね。

Twitter:
Warning: Undefined array key "Twitter" in /home/sazaeau/mizoshiri.com/public_html/blog.mizoshiri.com/wp-content/plugins/sns-count-cache/sns-count-cache.php on line 2897
0 | Facebook: 0 | Google Plus:
Warning: Undefined array key "Google+" in /home/sazaeau/mizoshiri.com/public_html/blog.mizoshiri.com/wp-content/plugins/sns-count-cache/sns-count-cache.php on line 2897
0 | Hatena: 1 | Pocket: 2 | Total: 3 | Feedly: 0