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

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

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

Q&A

解決済

4回答

22562閲覧

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

hpptms

総合スコア54

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

1グッド

3クリップ

投稿2016/09/30 15:38

編集2016/10/03 08:25

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

local.sql

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

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

aws.sql

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

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

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

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

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

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

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

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

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

bash

1#!/bin/bash 2 3dbuser="hoge" 4dbconection=`cat ./db_conection` 5dbname=`cat ./db_name` 6dbpassword=`cat db_password` 7path=`pwd`"/import/" 8datpath=$path"data/" 9 10psql -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
suama👍を押しています

気になる質問をクリップする

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答4

0

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

sh

1psql 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に対しての要求になりクライアントの入出力が可能となります。

bash

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

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

bash

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

投稿2016/10/01 02:49

編集2016/10/01 17:12
A.Ichi

総合スコア4070

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hpptms

2016/10/01 10:36

解答のお礼が遅くなってしまい申し訳ありません。 copyコマンドでもstdin、stdoutが使えるんですね。 私の質問の説明不足とサーバ、postgreの知識が足りていないため、理解出来ているか自身が持てませんが、SQL内でもstdin、stdoutが使えるんですね。 上記の質問をしたのが会社であったため、すぐに試せないのですが、 「:inputfile」をstdinに入力して、stdoutで文字列を出力してあげればいけそうな気がしてきました。 ご回答ありがとうございました。
A.Ichi

2016/10/01 16:08

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

0

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

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

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

投稿2016/09/30 20:30

退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hpptms

2016/10/01 11:29

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

0

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

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

投稿2019/06/03 14:56

nakamurakko

総合スコア10

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

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/09/30 21:04

suama

総合スコア1997

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hpptms

2016/10/01 11: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についてのアドバイスも頂きありがとうございました。
suama

2016/10/01 23:21

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問