CODEWITHSUNDEEP
postgresqlredis
Yilmazerhakan
Yilmazerhakan

Reputation: 1875

Redis Mass Insertion from Postgresql file

Hello I am trying to migrate from Mysql to Postgresql. I have an SQL file which queries some records and I want to put this in Redis with mass insert.

In Mysql it was working below this sample command;

sudo mysql -h $DB_HOST -u $DB_USERNAME -p$DB_PASSWORD $DB_DATABASE --skip-column-names --raw < test.sql | redis-cli --pipe

I figured out test.sql file for Postgresql syntax.

SELECT 
  '*3\r\n' ||
  '$' || length(redis_cmd::text) || '\r\n' || redis_cmd::text || '\r\n' ||
  '$' || length(redis_key::text) || '\r\n' || redis_key::text || '\r\n' ||
  '$' || length(sval::text) || '\r\n' || sval::text || '\r\n'
FROM (
  SELECT
  'SET' as redis_cmd,
  'ddi_lemmas:' || id::text AS redis_key,
  lemma AS sval
  FROM ddi_lemmas
) AS t

and its one output like

"*3\r\n$3\r\nSET\r\n$11\r\nddi_lemmas:1\r\n$22\r\nabil+abil+neg+aor+pagr\r\n"

But I couldn't find any example like Mysql command piping from command line.

There are some examples that have two stages not directly (first insert to a txt file and then put it in Redis)

sudo PGPASSWORD=$PASSWORD psql -U $USERNAME -h $HOSTNAME -d $DATABASE -f test.sql > data.txt

Above command working but with column names which i dont want.

I am trying to find directly send output of Postgresql result to Redis.

Could you help me please?

Solution:

If I want to insert with RESP commands from a sql file. (with the help of @teppic )

echo -e "$(psql -U $USERNAME -h $HOSTNAME -d $DATABASE -AEt -f test.sql)" | redis-cli --pipe

Upvotes: 1

Views: 328

Answers (1)

teppic
teppic

Reputation: 7286

From the psql man page, -t will "Turn off printing of column names and result row count footers, etc."

-A turns off alignment, and -q sets "quiet" mode.

It looks like you're outputting RESP commands, in which case you'll have to use the escaped string format to get the newline/carriage return pairs, e.g. E'*3\r\n' (note the E).

It might be simpler to pipe SET commands to redis-cli: 

psql -At -c "SELECT 'SET ddi_lemmas:' || id :: TEXT || ' ' || lemma FROM ddi_lemmas" | redis-cli

Upvotes: 1

Related Questions