7 Replies Latest reply on Feb 25, 2016 8:14 AM by ceath

    Accessing data on Amazon Redshift via ODBC

    ceath

      We have a client that maintains a fairly large data warehouse on Amazon's Redshift service.  We're now being asked to build a FileMaker solution that will access that data.  We have the FileMaker solution built (hosted on our own FileMaker Server 14 server) and we now need to access the data on Redshift.  Does anyone have experience connecting via ODBC to Redshift?  Is there any documentation on that yet?  We feel like we're close, but the Amazon documentation doesn't know about FileMaker, and the FileMaker ODBC documentation doesn't specifically mention Redshift so we think we're just missing something.

       

      Advice on how to set up FileMaker to access Amazon Redshift via ODBC would be most appreciated.

        • 1. Re: Accessing data on Amazon Redshift via ODBC
          mikebeargie

          Are you able to successfully configure a system DSN that accesses RedShift on your filemaker server? That's the first step. From that point it's pretty much the same thing to access that data from filemaker regardless of the platform you are using. However only specific data sources are supported via ESS for placement in filemaker's relationship graph, and redshift isn't one of them. That means you may have to use one of the other methods of accessing ODBC in filemaker, such as the Execute SQL script step.

           

          RedShift has both 32 and 64 bit ODBC drivers. You didn't mention what OS or bit version of filemaker you are running.

           

          Having filemaker pro installed on the same server hosting filemaker makes troubleshooting and setting up ODBC connections a lot simpler.

          • 2. Re: Accessing data on Amazon Redshift via ODBC
            ceath

            Thanks, Mike.  My strategy had been to establish the connection first on a client computer running FileMaker Pro 14 Advanced.  Then take my learning there and move it to the FileMaker Server 14 box.  I don't have Advanced running on the Server box (per FileMaker's recommendation) -- thus the reason for the strategy.

             

            I'm running both the Server box and the client boxes using Mac OS X 10.11.2 (El Capitan).

             

            The fact that I can't place a Redshift file on the relationship graph is disappointing, of course.  That would have been easier.  However, I can probably use the Execute SQL script step (but I'll have to bone up on my SQL -- it's been a while).

             

            Thanks for the advice.  If I get stumped, I'll post another response here.

            • 3. Re: Accessing data on Amazon Redshift via ODBC
              mikebeargie

              I'd recommend getting an ODBC manager, this one is standard:

              Mac OS X: ODBC Manager

               

              Then from the redshift documentation, you will need the OSX driver for ODBC:

              Install the Amazon Redshift ODBC Driver on Mac OS X - Amazon Redshift

              You will install the driver according to the directions there.

               

              After that, you need to create a DSN, what your system uses to store the connection details to your redshift database. Follow the redshift documentation for setting that up:

              Configure the ODBC Driver on Linux and Mac OS X Operating Systems - Amazon Redshift

               

              The ODBC manager should have a test button that allows you to test your DSN connection to redshift successfully.

               

              Once you have established a successful DSN, THEN you can start connecting to that DSN as a data source from FileMaker.

              • 4. Re: Accessing data on Amazon Redshift via ODBC
                wimdecorte

                ceath wrote:

                  However, I can probably use the Execute SQL script step (but I'll have to bone up on my SQL -- it's been a while).

                 

                 

                Keep in mind that there are two relevant script steps to use:

                - Execute SQL is for pushing data into the other database (can't use it to retrieve data)

                - Import (from ODBC source) is for pulling data into FM

                • 5. Re: Accessing data on Amazon Redshift via ODBC
                  ceath

                  Thanks, Mike.  I had found most of that documentation, but had missed the crucial first step, i.e. getting the Redshift driver into the ODBC manager.  I'm not there yet, but continue to feel like I'm making progress.

                   

                  I appreciate the advice.

                  • 6. Re: Accessing data on Amazon Redshift via ODBC
                    ceath

                    Thanks, Wim.  Good reminder.  In this particular app, I'll be doing 100% read only, so I'll use the Import command.  That will actually mean fewer changes to my system.  Right now I'm using the Import command on data I download via SFTP, so my sense is that the flow of the system won't change all that much once I get it connected to Redshift.

                    • 7. Re: Accessing data on Amazon Redshift via ODBC
                      ceath

                      Just to update everyone on the Redshift integration into FileMaker saga:

                       

                      The standard ODBC Manager most Mac users use is not supported by AWS Redshift.

                       

                      One must use, instead, iODBC Administrator (from iodbc.org).  However, iODBC Administrator crashes with El Capitan.

                       

                      The workaround is to manually place the configuration files (there are three:  amazon.redshiftodbc.ini, odbcinst.ini, and odbc.ini) in locations not documented by AWS Redshift (~/Library/ODBC/ and ~/user/<username>/Library/ODBC/).  The files must also be manually edited -- iODBC Administrator will mess up the files and crash if you try to use its user interface.  (I had to buy AWS support to find all of this out, although the folks that have been helping have worked hard to figure all this out.)

                       

                      I have finally been able to successfully connect to Redshift via iODBC Administrator.  I got a successful test message.

                       

                      However, I am now trying to attach via FileMaker and am coming up empty.  I get a message from FileMaker that this ODBC data source is not supported.  As wimdecorte notes above, this is not unexpected (although disappointing).  So now I'll be working on using the Import command to see what I wan work out.