Toad World Blog

Amazon Redshift queries to run?  2 data-agnostic tools

Feb 11, 2020 3:00:00 AM by Tim Fritz

As I have described in another recent blog post, “What is structured data and its hold on DBAs about?” on this blog, structured data is here to stay, even as unstructured data becomes more prevalent and important, according to research by By Dr. Elliot King, research analyst at Unisphere Research, a Division of Information Today, Inc. See the report, "DBAs Face New Challenges: Trend in Database Administration." 


A graph from research shows that the main reason that IT organizations are asked to support new database management systems is for analytical use cases.

 The infographic below reflects some of his key findings.

Infographic

While we know that structured data is still going to take the majority of DBAs’ time in the foreseeable future, we are watching another trend in the industry that affects those who need to use data: cloud data warehouses are growing in popularity as platforms for analytical use cases. 

Much of the data that will be used for analytics is structured data, and there are many ways to query that type of data.  The language that data professionals are most familiar with is SQL.  In this post, let’s examine who will need to access the data for analytics purposes, and ways they can do that using products that they might already be using for other database platforms.

 

Experiments with analytical data—an important step in data science and gaining insights

There is something in the area of 2.5 quintillion bytes of data generated every day.  To make use of all that data, databases are a must.   And, more and more, data science is being applied to ensure that the best insights for business decision-making are being discovered.  Data science is analytics with a rigorous and repeatable cycle of:

questions > hypotheses > data > experiments > insights.

One stage of that process is “data” and another is “experiments”.   Data has to be collected and integrated somewhere.  Data scientists will often be given a high degree of autonomy by management to go find impactful insights in data.  The data in their data lake, data mart, or data warehouse has to be accessed.

Data warehouses are a growing destination for much of the data being used in data analysis for business insights.   Many data warehouse databases are relational – whether row-oriented or column-oriented.   And, as we mentioned above, structured data is expected to rule data managers’ time for the foreseeable future.  So, it is no wonder that SQL (Structured Query Language) is still one of the top 3 skills required of data scientists.

Data warehouses are moving to the cloud, too, to take advantage of cost efficiency, on-demand scalability, bundled capabilities provided by the cloud vendor, security and system uptime and availability.  And, many of the cloud data warehouses have the advantage of supporting direct queries of their data using SQL.

 

Running queries against a cloud data warehouse or a traditional relational database

As mentioned, the data in the data warehouse is of no use if nobody can get to it.  SQL is a language that many people know to some degree, but to write queries that will have the correct syntax, and the behavior in returning results that you expect, why not use a SQL development tool.  You might be surprised to learn that the Quest® Toad® family of products do, indeed, provide the capabilities to develop queries and execute them against:

  • Traditional relational database types using Toad
  • A wide array of database and files types using Toad Data Point
  • Open source databases MySQL and PostgreSQL using Toad Edge
  • Leading cloud data warehouse, Amazon Redshift also using Toad Edge (beta) or Toad Data Point!

 

So, data housed in an Amazon Redshift cluster can be queried, using SQL, the same way you do with Quest tools for other database types—traditional and otherwise.

Brief analysis through initial queries can begin to give data analysts and business managers a feel for the data.  SQL might be valuable in running queries as they “pre-process” the data.    

 

Toad Edge® connects to Amazon Redshift cluster (beta available in product now)

As of this writing in February 2020, Toad Edge now has the ability to connect to not only MySQL and PostgreSQL, but also to Amazon Redshift cluster thanks to an in-product beta (it's functional in the downloaded product - give it a try).  Here’s a screen capture showing the results of a SQL statement coded and then executed within the Toad Edge client application, accessing data from Redshift.   

Screen capture showing the results of a SQL statement coded and then executed within the Toad Edge client application, accessing data from Redshift.  

The interface in Toad Edge SQL Editor is the same no matter which of the supported database types is the target of the query.   So even people new to Redshift data warehouse can feel comfortable running SQL to examine data. 

 

Want a gold-star understanding?

Some Toad World® videos might be helpful to you.

 

Toad Data Point is your window into many data sources, including Redshift

This product is the Quest tool for data analysts.  It’s a data query and preparation tool, and it can make your life a whole lot easier if you’d like to take initial looks at the data on Redshift tables.  You could take things to another level by performing some preparation tasks on the data before exporting it out to a file, for example.

Here is a screen capture showing the results of running a SQL query against Redshift in Toad Data Point:

Screen capture showing the results of running a SQL query against Redshift in Toad Data Point.

 

Like the Toad Edge example previously, this editor and results tab report the execution duration and the date and time the query was executed. 

Want a gold-star understanding?

To get a gold star in Toad Data Point, the following videos on Toad World might help you:

Getting Started: Getting Started with Toad Data Point

Connections to data sources: Video Series: Mastering Connectivity in Toad Data Point

Writing queries: Writing Queries in Toad Data Point

SQL Templates: Toad Data Point SQL Templates

See all the Toad World videos about Toad Data Point.

Visit our product page and learn more about Toad Data Point, a powerful tool that will help you access and prepare data for faster business insights. 

 

 

Get features that save time and optimize your SQL

These features add up to time-savings and optimal effectiveness in writing and executing SQL, even against your Redshift data warehouse.  If you already use Toad Edge or Toad Data Point, the interface will be familiar for Redshift queries, too.

 

Got questions?

If you have any questions, please post questions to the Toad Data Point forum on Toad World.

 

Like this post?

You can instantly share it with your colleagues using the social media instant-share buttons at the top of this post.

Read more blogs from Tim.

 

 

 

Tags: Toad Data Point Toad Edge

Tim Fritz

Written by Tim Fritz

Quest Software solution architect - database performance management and administration solutions. Former DB2 developer and DBA. IBM Certified Database Administrator – DB2 LUW; Microsoft MCP, Certified Technical Specialist – SQL Server.