0

I am trying to connect R to Redshift using dplyr and RPostgreSQL based on the method from https://blogs.aws.amazon.com/bigdata/post/Tx1G8828SPGX3PK/Connecting-R-with-Amazon-Redshift

Now I know my database, host port, user and password. So, here is my code:

> myRedshift <- src_postgres('aaa',
                            host = 'aaa-aaa-aaa.com',
                            port = 8000,
                            user = "xy", 
                            password = "xy")

After running this line, I have a list myRedshift. It looks like OK. But, when I run:

  a <- tbl(myRedshift, "base_posdata")

The problem is: the database aaa includes a couple of folders like a1, a2, a3. The table base_posdata is under a1. So it is supposed to be like: 

a <- tbl(myRedshift, "a1//base_posdata")

Of course the format is not correct. So when I use the above code (a <- tbl(myRedshift, "base_posdata") ), I have the error message:

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR: relation "base_posdata" does not exist)

I guess this is because I did not provide the right path. Anyone can tell me how to do this? Thanks a lot.

Feng Chen
  • 2,139
  • 4
  • 33
  • 62
  • Postgres' database model include databases, schemas, tables, and columns. Make sure you are addressing each correctly in your query. – Tim Biegeleisen Oct 11 '16 at 05:25
  • If I use Alteryx to connect the data in Redshift. I can select the data using the following way: 1. choose table or specify query, 2. click "Tables" button, 3. choose the table which is shown as "aaa"."a1"."base_posdata". Could you please explain if this can imply it is a table or sth else? And how can I deal with this? Thanks – Feng Chen Oct 11 '16 at 05:33

1 Answers1

1

Just find a way to deal with this: using sql like:

> a <- tbl(myRedshift, sql("SELECT * FROM aaa.a1.base_posdata"))

problem solved

Feng Chen
  • 2,139
  • 4
  • 33
  • 62