Using the Github Repository and Community Queries for Aginity Pro and Team

Recently Aginity released a series of SQL catalogs compatible with Aginity Pro and Team on Github. These catalogs are pre-built, ready to use queries against our most popular supported databases.

How do I get Started

Head right over to Github and read the How to Import documentation.

How do I Contribute

You have several options to help out.  
  1. Our GitHub repository is public so you can create a fork, create your own catalog, suggest modifications to ours, commit it, request a pull and we’ll take if from there!  Here is quick video on getting started with GitHub if you want to give this a try!
  2. The easiest way is to send us an email at query@aginity.com.  When you do this it will submit a ticket to us and we will take a look, ask/answer any questions and make sure the query gets put out in the community!
  3. Finally, feel free to post packages directly to our community as well.  We constantly monitor this site and if we like what you posted we will incorporate it into the repository!

Background

Within each Github repository you’ll find administrative, data engineering, analytic functions and analytic queries ready to execute.  Our goal with Aginity Pro and Team is to create efficiency in the SQL development community by promoting, sharing and reusing common queries and query patterns through our active catalog.
 
For example in the administrative catalogs we have prepared queries to do things such as dropping unused tables in a database.  All of these queries interrogate the platforms data dictionary tables and will generate or execute the SQL scripts immediately.  In our data engineering catalogs we may have scripts to help generate time and date dimension tables for analytic users to use to facilitate analysis.  In our analytic catalogs we typically have executable examples that show you how to do complex things in SQL such as creating a linear regression analysis data.
 
Here is a sneak peak of some of the queries we have available:
 
blobid0.png
 

What’s Next

Our support and services team continue to catalog queries, validate them and check them into the Github repository on a weekly basis.   We are now ready to start engaging with our Pro and Team users to curate this catalog and work together as a community.  
 
As we continue to learn how to best use the active catalog we allocate time to enable the community with our catalogs.  Here are some of the areas we are focused on.
 

Analytic Functions

If you are like us you are constantly searching for SQL syntax or referencing SQL reference manuals. We are starting to catalog the most popular SQL functions that can be dragged into the query editor. The image below shows how we do this with the percentile_cont function in Redshift.  On the left you can see the library of functions we put at your fingertips in the catalog.
 
blobid1.png
 

Analytic Examples

We can’t and don’t want to ship data back and forth so our goal is to write powerful SQL statements that you can easily translate to your data.  Our analytic examples do just that.  Shown below is a linear regression analysis that uses a catalog of SQL based math disconnected from the database.  Provide the table names when prompted and you’ll be predicting growth or decline in whatever metric you choose to analyze!

 
blobid2.pngblobid3.png
 

Administrative Functions 

Whether you are a DBA, data engineer or analyst you will be faced with the need to interrogate the data dictionary and will find yourself spending hours researching how to do this.  We are packaging up the most functional queries and making them available to the community.  One of my favorites shown below is the ability to generate ‘drop table’ statements based on a table name pattern.  As a data engineer I am always needing to keep my sandbox clean as I create data sets.
 
blobid4.png
 

Data Engineering Functions 

 
Let’s say you want to aggregate your analytics up to a week and want to display a start date but don’t have that handy date dimension laying around.  That’s OK! We have a catalog query for that to use. The example below easily generates a time dimension.    Just drag over the item and run the queries in sequence and voila, time date_dim!
 
blobid5.png

 

 
Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.