質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.21%

posgresql \copyのfromに変数(csvのパス)を使いたい

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 3
  • VIEW 14K+

hpptms

score 55

AWSのRDSでpostgresqlを使用しております。
定期的なデータ更新があり、シェルスクリプトとSQLで更新環境を作成しております。
ローカル(vagrantで作成したcentos6)で試していた際はsuperuser権限が使用出来たため、下記の様な書き方で実行しておりました。

\set :inputfile ←シェルから変数で渡しています。
copy hoge from :inputfile (format csv, header true);

ですがAWSのRDSだとsuperuserのロールが作れない、もしくは作るのが難しいため、下記のように書き変えました。
ですが「from :inputfile」の「:inputfile」の中のパスが展開の仕方が分からず困っております。
エラー内容も[:inputfile: No such file or directory]でおそらく「:inputfile」が展開されてないのではと思っております。※なぜか「末尾に:がついているのも気になりますが・・・」

\set :inputfile
\copy hoge from :inputfile with csv HEADER DELIMITER ',';


自分なりに調べた内容は以下の点です。
・ファイルのパスを直打ちで'hoge.csv'とすると正常に動作する。
・「/echo :inputfile」で出力されたパスは表示され catなどで確認しても中身はある。

もう少しで正常に動作させられそうなのですが、どうしてもfrom の部分の書き方が分からず質問致しました。
どなたか答え、もしくはヒントでも良いのでお助け下さい。

お礼と補足:
大変申し訳ございませんが、手元に実行環境が無いため検証できしだい、ベストアンサーを選択致します。
また、質問内容が分かりにくく申し訳ありません。

構成としては、「.sh」でCSV手前までのパスを定義して、そのファイル内でpsqlに接続し、一緒に実行する外部SQLファイル、変数を渡しています。

おそらく何通りも答えがありそうな予感がしております^^:
ベストアンサーはまだですが、先にみなさまアドバイスありがとうございました。

結果:
いくつかの方法を提示して頂けましたが、ファイルパス直打ちにしました。
アドバイス頂けたのに申し訳ないです。
中には私の理解不足で上手く試せてない方法もあります。

その他自分で試した方法として、一時テーブルを作成してサブクエリでfromを指定したりしてみましたが、ダメでした。

ベストアンサーを選んでからなので少し遅いですが、詳細な方法としては、下記の通りです。

#!/bin/bash

dbuser="hoge"
dbconection=`cat ./db_conection`
dbname=`cat ./db_name`
dbpassword=`cat db_password`
path=`pwd`"/import/"
datpath=$path"data/"

psql -h $dbconection -U $dbuser -d $dbname -f $path'sql/import.sql' -v datpath=$datpath
\set filename hoge.csv
\set inputfile '''':datpath:filename''''

\copy hoge from :inputfile with csv HEADER DELIMITER ',';
-- 上記の:inputfileがどうしても展開されず・・・

おそらくスクリプトの方を工夫することで問題の解決は図れそうですが、外部SQLの中が結構複雑で時間を考えて直打ちになりました。
せっかくアドバイス頂いたのに解決できずに申し訳ないです。

結果2
何度も申し訳ありません。
\copyコマンド自体をinputするという方法を上司に指南して頂きました。
スラッシュを2重にしないとダメなようでした。
ニッチすぎる情報ですが、誰かのお役に立てば。

\set copycmd '\\copy hoge from ':inputfile' (format csv, header true);'
:copycmd
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

+3

copyコマンドでもstdinで行えます

psql hogedb -c"copy tablename FROM stdin with csv HEADER DELIMITER ','" <hoge.csv

\copyで使えないオプションが使えます。
実はstdinとstdoutは\copyコマンドでも同様に可能です。

※なぜか「末尾に:がついているのも気になりますが・・・」

copyはpostgresサーバへのコマンドSQL文と同じなので、;が必要です。
\copyはpsqlクライアントへのコマンドなのでsetと同様に不要です。
copyはリモートサーバで実行され入力ファイルは、そのサーバで参照可能な場所になります。
stdin,stdoutはサーバがクライアントのpsqlに対しての要求になりクライアントの入出力が可能となります。

#!/bin/sh
inputfile=hoge.csv
psql hogedb -c"\copy hoge from '$inputfile' with csv HEADER DELIMITER ',';"

要望されているのと違いますがstdinを使った場合

#!/bin/sh
psql hogedb<<EOD
\set inputfile stdin
copy hoge from :inputfile with csv HEADER DELIMITER ',';
$(cat hoge.csv)
\.
EOD

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/10/01 19:36

    解答のお礼が遅くなってしまい申し訳ありません。

    copyコマンドでもstdin、stdoutが使えるんですね。
    私の質問の説明不足とサーバ、postgreの知識が足りていないため、理解出来ているか自身が持てませんが、SQL内でもstdin、stdoutが使えるんですね。

    上記の質問をしたのが会社であったため、すぐに試せないのですが、
    「:inputfile」をstdinに入力して、stdoutで文字列を出力してあげればいけそうな気がしてきました。
    ご回答ありがとうございました。

    キャンセル

  • 2016/10/02 01:08

    たぶん\set inputfile stdin とcopyコマンドはできるとおもいますが、その後にheaderとデータを入力する必要が有りますcsvで。さらに最終行に\.を入力して入力ファイル終了とします。

    キャンセル

+1

シェルスクリプトで変数を展開するにはこちらが参考になるかもしれません。
http://qiita.com/bsdhack/items/597eb7daee4a8b3276ba

次の書き方ではどうでしょう。

\copy hoge from ${:inputfile} with csv HEADER DELIMITER ',';

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/10/01 20:29

    解答のお礼が遅くなってしまい申し訳ありません。

    ズバリ答えを教えていただきありがとうございます。
    posgresqlについてまだ知識が浅く、SQL内で出来ることがまだまだ理解できていないです^^;
    月曜出社したらすぐに試してみます。

    キャンセル

checkベストアンサー

0

こんにちは。
copyコマンドはDBのサーバ側のファイルシステムにファイルが置かれている(もしくは書き込みできる)のが前提なので、AWS RDSでは使えないという認識でいます。

そのため、¥copy コマンドを利用されたということですね。

シェルスクリプトとSQLで更新環境を作成

とありますが、shell (bash?) を使っているのでしたらpsqlの\setを経由しなくとも、bashの変数でファイル名を指定するのはだめでしょうか?
具体的にやりたいことがわかるといいのですが、推測の上でこんな作業でしょうか。

ワンライナーの例:

$ export tablename=foo
$ export inputfile=hoge.csv 
$ psql -d hoge -c "\copy $tablename FROM $inputfile with csv HEADER DELIMITER ','"
$ psql -d hoge -c "select * from $tablename"
 id | name 
----+------
  2 | var
  3 | test
  4 | tare
(3 rows)

作業用のディレクトリの *.csvを全部インポートしたいとかの場合(shell scriptを作成)

#!/bin/sh 
DBNAME=データベース名
TABLENAME=対象テーブル名
# ¥copyコマンドなので末尾の;は要らない
for INPUTFILE in *.csv 
do 
   psql $DBNAME -c "\copy $TABLENAME FROM $INPUTFILE with csv HEADER DELIMITER ','" 
done

外していたら申し訳ありません....。

なお、

AWSのRDSだとsuperuserのロールが作れない、もしくは作るのが難しい

こちらについてですが、RDSのDBはデータベースのサービスだけを利用できるものなので、サーバ側のファイルシステムを利用する copy コマンドは利用できないと思います。(もし使うのであれば、標準出力でインポートする内容を転送するかたちで流し込むとか)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/10/01 20:17

    解答のお礼が遅くなってしまい申し訳ありません。

    シェルスクリプトはbashで書いております。
    私の説明不足で申し訳ありませんが、hoge.sh内で変数を定義して、
    そのhoge.sh内でpsqlをSQLファイルと定義した変数を渡して実行しております。

    渡す変数の中身は[/home/hoge/]などのcsvファイルの手前までのパスです。
    その変数とcsv名(こちらはSQLファイル内に直書き)を連携して、下記の様にしておりました。(手元にファイルが無いのと、前任者の方から引き継いだファイルを改造していたため、文法の間違いなどあるかもしれませんが、ご容赦ください)

    \set csvfile 'human.csv'
    \set targetfile '':get_sh_input:csvfile:csvfile''
    ↑上記を利用して「copy」から「\copy」に書き換えようとしていました。

    質問をしたのが会社ですぐに試せませんが、何とかなりそうな気がしてきました。
    またRDSについてのアドバイスも頂きありがとうございました。

    キャンセル

  • 2016/10/02 08:21

    具体的に書いてくださってありがとうございます!
    解決方法もできそうで何よりです。上の方が書いてくださっていましたが、こちらこそ色々変数の受け渡し方法があるんだと勉強させていただきました。

    キャンセル

0

書きました。
psql経由のCSV出力で任意のファイル名を渡す

copyで :'inputfile' のように、シングルクォーテーションで囲めば出来ると思います。
(\copyは出来ませんでした。)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.21%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る