where LinuxコマンドラインからMySQLクエリを実行し、CSV形式で結果を出力する簡単な方法はありますか? これが私が今やっていることです: mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | tee list. INTO OUTFILEこれは実際によく遭遇する問題です。たとえば、 データベースをCSVにエクスポートを参照してください。 それぞれのAWSチームの反応は、そうした輸出を防ぐサーバーアクセスがないというあなたの前提を確認し、mysqlコマンドラインクライアントのデータを選択して出力をパイプラインしてデータを再フォーマットすることで、CSV形式でデータをエクスポートするCSVのように、そう:, ユーザーfpalero は、フィールドを事前に把握して指定している場合は、代替アプローチと思われる簡単な方法を提供します。, 私はRSI mySQLに接続するEC2のYii Frameworkを使用しています。 キーはfputcsv()を使うことです。 以下は、ローカルホストとプロダクションの両方で、完全に動作します。, ホスト 'xxx.xx.xxx.xxx'はこのMySQLサーバに接続することができません. DBサーバーがリモートホスト上にある場合はこの方法が使えません。, いろいろ調べた結果たどり着いたのが以上の方法です。 Closing thoughts: MySQL LOAD DATA Docs; AWS' Aurora RDS is MySQL-compatible so command works there too "LOCAL" flag actually transfers the file from your client … EC2からRDSに接続してmySQLを操作したいと思いましたので、構築までの流れを備忘録として残したいと思います。EC2は通常のWebサーバーではなく、mySQLを操作する一時的なサーバー(踏み台サーバー)として立ち上げます。 mysqlのデータをcsvファイルに出力したい場合、 select … into outfile 構文を使うのがよくあるパターンなのですが、 dbサーバーがリモートホスト上にある場合はこの方法が使えません。 … read more → 例えばsedでやるならこうなります。, スマートな方法は見つからなかったのですが、 今回は3と4の手順について、まとめたいと思います。 簡単にまとめると、踏み台サーバーのec2にcsvファイルをscpコマンドで転送し、rdsのmysqlでec2にあるcsvファイルをload data local infileで一括追加してみたいと思います。 構成図 SELECT * FROM my_table WHERE my_attribute = 'my_attribute'; クライアントホストのファイルパスを探しても、CSVファイルは出力されていません。 s/^/\"/ at the beginning of the line place a ", "/ at the end of the line place a ", --database=*database_name* -u *username* -p -e *your SQL query* | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > *output_file_name.csv*, """ ⑥ ec2インスタンスにcsvファイルが転送されたことを確認します。 以上で、ec2インスタンスへのcsvファイルの転送は終了です。 ec2インスタンスにmysqlクライアントをインストールし、rdsのmysqlデータ … RDSへ、Excelでもらったマスタデータを取り込む時にいろいろとハマったので書いておこうと思います。 RDSでmysqlimportは使えない、LOAD DATA LOCAL INFILEを使う まさにこちらの記事と同じ手順を辿りました。 RDSでcsvファイルをインポートしようとして権限エラーになったときの対応 -… $ scp -P 22 -i AWS_Key.pem sample.csv ec2-user@12.34.567.89:/home/ec2-user/, $ ssh -i AWS_Key.pem ec2-user@12.345.67.78, Last login: Tue Aug 30 01:12:34 2016 from abcd1234.hoge.ne.jp, https://aws.amazon.com/amazon-linux-ami/2016.03-release-notes/, [ec2-user@ip-10-0-0-4 ~]$ mysql -h testDB.hoge12345.ap-northeast-1.rds.amazonaws.com -P 3306 -u yourName -p, Records: 7323 Deleted: 0 Skipped: 0 Warnings: 0, Records: 7323 Deleted: 0 Skipped: 0 Warnings: 2, +---------+------+--------------------------------------------+, | Level | Code | Message |, dogmap.jp を t1.micro から t2.micro に変更してみました, CloudWatchグラフの確認方法と確認できるグラフ一覧(EC2/ELB/RDS), 負荷が高くなく CPU がベースライン性能にとどまっている場合、CPUクレジット残高が貯まっていく. $ echo "SELECT * FROM Table123" | mysql Database456 | tr "\t" , perlスクリプト(他の場所から抜粋)は、タブで区切られたフィールドをCSVに変換する優れた機能を果たします。, 正確にはCSV形式ではありませんが、MySQL クライアントからのteeコマンドを使用して、出力をローカルファイルに保存できます。, の問題SELECT … INTO OUTFILE …;は、サーバーでファイルを書き込む許可が必要なことです。, Timが投稿したソリューションを使用して、プロセスを容易にするためにこのbashスクリプトを作成しました(rootパスワードが要求されますが、スクリプトを簡単に変更して他のユーザーに要求することができます)。, サーバーでPHPを設定している場合は、mysql2csvを使用して、任意のmysqlクエリ用の(実際に有効な)CSVファイルをエクスポートできます。MySQLで私の答えを見てください-SELECT * INTO OUTFILE LOCAL?もう少しコンテキスト/情報のために。, オプション名を維持しようとしたmysqlので、--fileおよび--queryオプションを指定するだけで十分です:, (要点のコンテンツをダウンロードし、チェックサムをチェックして実行可能にします)。, このスレッドの解決策はどれも私の特定のケースでは機能しませんでした。列の1つにかなりのjsonデータがあり、csv出力でめちゃくちゃになります。同様の問題がある場合は、代わりに\ r \ nで終了する行を試してください。, また、Microsoft Excelでcsvを開こうとする場合の別の問題として、1つのセルが保持できる32,767文字の制限があることに注意してください。これを超えると、下の行にオーバーフローします。列のどのレコードに問題があるかを特定するには、以下のクエリを使用します。その後、それらのレコードを切り捨てるか、必要に応じて処理できます。, そのときにエラーが発生する場合はsecure-file-priv、宛先ファイルの場所を内部に移動したC:\ProgramData\MySQL\MySQL Server 8.0\Uploads後も、クエリの後に. mysql> LOAD DATA LOCAL INFILE 'C:/upload.csv' INTO TABLE myTable FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES; If you're hesitant to use CL, download MySQL Workbench. リモートサーバー(特に、HerokuやAmazon RDSなどのホスト型または仮想化マシン)からローカルマシンに出力を書き込みたい場合、このソリューションは適していません。 1540 . 理由とか気にならない人は、ここ以下の内容を読む必要はないですw, MySQLデータベースから、SELECT文の実行結果をCSVファイルとして取得する場合、 SELECT * FROM attendance INTO OUTFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\FileName.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; が機能していない場合は\、クエリから(バックスプラッシュ)を変更するだけです/(forwardsplash)に. © 2016 - 2020  おちゃカメラ。  /  Built by hexo. csv ファイルにない値を挿入. """, # embedding passwords in code gets nasty when you use version control, # the environment is not much better, but this is an example, # http://stackoverflow.com/a/17725590/2958070 about lineterminator, 'print join ",", map {s/"/""/g; /^[\d. リモートDBの結果をどうしてもクライアントホストにCSV出力したい場合は参考にしてみてください。, http://shusatoo.net/db/mysql/mysql-remote-db-result-output-local-csvfile/, MySQL の結果を csv 形式で標準出力させたい – BOOLEANLABEL, Vagrant + chef-solo によるPHP + MySQLな開発環境構築入門, Backbone.js Collection.fetch() による一覧画面の描画, Backbone.js Model.save()によるPOSTデータをPHPで読み込む, Backbone.jsのsaveでPOSTした値をPHPの$_POSTで取得できない… | OnederAppli, FuelPHPのRestコントローラでBackbone.jsからPOSTのデータを受け取る方法 | Bamboo lath 日々の記録, 【Go】DBマイグレーションツールGooseをちょっと試す | Yohei Blog. INTO OUTFILE ではうまくいかなかったので。まずは出力SQLを記述 $ vi /path/to/sqlfile.sql select ... ; -eでSQLをコンソール実行。awkでtsvをcsvに。 $ mysql database -hremotehost -uusername -ppassword -… case 文を使う。 SELECT $cols FROM $table INTO OUTFILE, --csv -e "select * from flowers" > flowers.csv, """ """, --php-ini path/to/php.ini your-script.php, "SELECT * FROM table_with_the_data p WHERE p.type = $typeiwant", stackoverflow.com/questions/3760631/mysql-delimiter-question, http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/, https://stackoverflow.com/a/5395421/2841607, s ///は、最初の//の間にあるものを2番目の//の間にあるもので置き換えることを意味します//. select col1,col2 from table この前の続き。RDSで立ち上げたmySQLサーバーに、大量のデータが入っているCSVファイルを一括で追加するまでの流れをまとめてみたいと思います。, photo by Mr. Alex Garcia - Double Cliche on flickr, https://tea3.github.io/p/aws-ec2-to-rds-connection/, 簡単にまとめると、踏み台サーバーのEC2にCSVファイルをSCPコマンドで転送し、RDSのmySQLでEC2にあるCSVファイルをLOAD DATA LOCAL INFILEで一括追加してみたいと思います。, 構成図は前回と同じで、SSHのみを許可した踏み台サーバーのEC2とRDSを立ち上げます。, EC2インスタンスが立ち上がったら、CSVファイルを転送します。転送にはSCPコマンドを使います。, エンドポイントは/home/ec2-user/と指定すると、EC2のルートディレクトリにCSVファイルが転送されます。転送後、EC2に入って、CSVファイルが保存されているか確認してみましょう。, lsコマンドで見てみると、ルートディレクトリにsample.csvが存在しているのが確認できました。, 続いて、RDSのmySQLに接続してみます。AWSコンソール > RDS > インスタンスからRDSインスタンスのエンドポイントを確認しておいてください。, ここで、データベースとテーブルが作成されていない場合は、予め作っておきましょう。仮にデータベース名をscoreDB、テーブル名をtestScore2016とします。, RDSに接続し、テーブルを作成したら、EC2にあるCSVファイルをインポートします。インポートにはLOAD DATA INFILE構文を使います。, 実行した結果がQuery OKと出れば、CSVファイルの追加は完了です。また、CSVの一行目を読み込みの対象外としたい場合には、IGNORE 1 LINESを指定します。, インポートするCSVファイルは、RDSで設定した文字コードと同じとなるuft-8で用意します。, mySQLにLOAD DATA INFILE構文を使って、データを投入するとエラーが出る場合があります。その時は、SHOW WARNINGS;でエラー内容を確認します。, 例えば、上記の場合では、CSVファイルの一部のデータに値が無いときに発生する警告です。今回はスペースを入れて対処しました。, LOAD DATA INFILEした直後のmySQLのCloud Watch監視メトリクスは以下のような感じになりました。メトリクスの見方は全て理解してしいないので、ひとまずデータだけ貼り付けておき、後で見返すようにしたいと思います。, dogmap.jpdogmap.jp を t1.micro から t2.micro に変更してみました, Developpers.IOCloudWatchグラフの確認方法と確認できるグラフ一覧(EC2/ELB/RDS), Developpers.IOAmazon Auroraの運用時に監視できる項目について, AWSのRDSで立ち上げたmySQLサーバーに、大量のデータが入っているCSVファイルを一括で追加しました。数千件のデータであれば、1秒未満で投入できるようです。, ゆるふわポートレートや自然風景をのんびり撮影しています。その他の趣味は音楽制作とメダカの鑑賞。撮影や写真掲載等のご依頼については、SNSまたはお問い合わせフォームまでお願いいたします。, コンテンツの保存及び転載は固くお断りいたします。当サイトに記載されるコンテンツ(文章・画像等)は著作権により保護されています。万が一コンテンツや写真の盗用・二次利用が見つかった場合には500pxの写真販売価格を参考に差止め請求及び使用料または損害賠償請求をさせて頂く場合がございますので予めご了承下さい。記事に関するお問い合わせや寄稿については上記のSNSまでご連絡をお願いいたします。, +---------+------+--------------------------------------------+, | Level | Code | Message |, dogmap.jp を t1.micro から t2.micro に変更してみました, CloudWatchグラフの確認方法と確認できるグラフ一覧(EC2/ELB/RDS), プロ・ブロガーの 必ず結果が出るアクセスアップ テクニック100 ファンにも検索エンジンにも好かれるブログ運営の極意, 負荷が高くなく CPU がベースライン性能にとどまっている場合、CPUクレジット残高が貯まっていく. CREATE TABLE () (SELECT data FROM other_table ) ENGINE=CSV ; CSVテーブルを作成すると、サーバーはデータベースディレクトリにテーブル形式のファイルを作成します。ファイルはテーブル名で始まり、拡張子は.frmです。ストレージエンジンはデータファイルも作成します。その名前はテーブル名で始まり、.CSV拡張子が付いています。データファイルはプレーンテキストファイルです。データをテーブルに格納すると、ストレージエンジンはデータファイルにカンマ区切り値形式でデータを保存します。, この回答では、Pythonと人気のあるサードパーティライブラリであるPyMySQLを使用しています。Pythonのcsvライブラリは多くのさまざまなフレーバーを正しく処理するのに十分強力で.csvあり、他の回答はPythonコードを使用してデータベースと対話しないため、これを追加します。, 次に、ハードディスクに常にCSV形式のファイルを作成します。このファイルは、処理せずにコピーできます。, 以前の回答を拡張するために、次のワンライナーは単一のテーブルをタブ区切りファイルとしてエクスポートします。自動化、データベースの毎日のエクスポートなどに適しています。, 便利なことに、同じ手法を使用してMySQLのテーブルを一覧表示し、単一のテーブルのフィールドを記述することができます。, user7610に基づいて、これを行う最善の方法を次に示します。とmysql outfile、ファイルの所有権と上書きの問題の60分がありました。, php csvdump.php localhost root password database tablename > whatever-you-like.csv, また、Bashコマンドラインでクエリを実行している場合、このtrコマンドを使用して、デフォルトのタブを任意の区切り文字に置き換えることができると思います。. mysqlのデータをcsvファイルに出力したい場合、 select … into outfile 構文を使うのがよくあるパターンなのですが、 dbサーバーがリモートホスト上にある場合はこの方法が使えません。 … read more →