If I am using Matillion...connected to a given Redshift "environment"...then is there a way to access data in another Redshift cluster? Or, the same Redshift cluster but a different database?
Because I think I would like to use Matillion to extract data from an API, and store the data permanently in a Redshift database that is separate from my Redshift data warehouse database. Because I want the entire data warehouse database to be quick and easy to delete and re-create, any time. But this data I am extracting from an API -- it takes many hours to extract it all. So I want to extract historical data from the API just once, and store it in a separate database, and consider that to be a "permanent" database -- I would not ever delete it.
Then, from the point of view of my data warehouse, I want to use that "API database" as a data source. So, in my Matillion jobs that populate the data warehouse, I will need to get data from a separate Redshift database.
Can I do that? Or, any other suggestions for my use case? Thanks!
2 Community Answers
Laura Malins —
You can do this at a schema level. In each Matillion Load/Unload component you have the option to select the schema you want to load the data into. However unfortunately this isn’t available at a database level.
You’ll be aware that you can use a different environment to load the data into your target database. I would do the historical load from the API just once with a different environment to your new API Redshift database.
You can then switch your environment to your usual one and use the Database Query component to move the data between the API Redshift database and your usual one. You can use the Postgres database driver or upload your own Redshift one.
Perfect, yes, I was wondering if I could just use the Database Query component.
I tried it with the included PostgreSQL driver, first -- that works. And, I figured out how to add a Redshift driver...that works, too. (I'll test both out -- maybe one will be faster/better, for my particular situation.)