Pull data from a table hosted in pltaform.sh

Arun Krishnan

Published August 16, 2024 16:03

The information in this post is accurate as of the published date August 16, 2024 16:03. Please make sure to check any linked documentation within the post for changes/updates.

I want to query a maria db table a hosted on Platorm.sh and bring the data to AWS . How to do this ? 

0

• 

  Paul Gilzow

  August 19, 2024 13:27

  If you are using our CLI, you can do

  > platform sql -e <environment-name> -p <projectid>

  which will drop you into a myql prompt.

  If you just need a local dump of the entire database, you can run 

  > platform db:dump -e <environment-name> -p <projectid>

  which will create a local *.sql file containing the contents, and from there should be able to import that into another system.

   

   

   

   

  0
• 

  Arun Krishnan

  August 19, 2024 14:32

  Hi Paul Gilzow, 

  Thanks for your response.

  But I do not want the entire database to be pulled. I want to programmatically pull data from a single table. For eg:  ‘select * from table1 where column1 = ’Y' . I want to use this SQL in a ETL tool or a Python script . How to establish this ? 

   

  0
• 

  Paul Gilzow

  August 19, 2024 15:26

  the platform sql command also allows you to pass a sql command to the db instance. From there, you could redirect the output to a file. Something like:

  > platform sql -e <environment-name> -p <projectid> ‘select * from table1 where column1="Y";’ --raw > somefile.txt

  alternatively, you can create a tunnel to the database service. 

   

  0
• 

  Arun Krishnan

  August 19, 2024 15:39

  @Paul Gilzow

  Again ,my question is,  How to embed this platform sql statement in a Python script or ETL tool (eg: Informatica/Alteryx)  and schedule it to run automatically from a remote server?

    

  0
• 

  Paul Gilzow

  August 19, 2024 16:45

  in terms of a specific ETL tool, I can't answer since I haven't used those tools. 

  As for python, you could either have python call the platform cli tool directly ( subprocess.Popen ). Or you could create a tunnel from the remote server to the platform db service and then connect to the db instance from your python code via the tunnel.

  You mention a remote server, so you could have a cron job that runs the platform cli command on a schedule dumping to a file, and then your python script (or maybe the ETL tools) could read from the file.  

  0

Please sign in to leave a comment.