Presto, Magico open source distributed SQL Engine

2017-04-25_11-23-16In today’s blog, I will be introducing you to a new open-source distributed SQL query engine, Presto. It is designed for running SQL queries over Big Data (petabytes of data). It was designed by the people at Facebook. Quoting its formal definition:

“Presto is an open-source distributed SQL query engine for running interactive analytic queries against data sources of all sizes ranging from gigabytes to petabytes.”

Th folks at Facebook are at it again.   They build a SQL engine especially for analytical work, this is not an online transaction processing (OLTP) engine.  It’s an engine for ad-hoc queries across SQL/NoSQL databases distributed all over the place.

They use connectors for MySQL, Hadoop/Hive, MongoDB, Postgres and more.  Missing are some of standards like Microsoft SQL and Teradata.  However, this won’t be the story for long.

Presto is in its open source newness but you should take a look at the documentation to really appreciate the power of this new thing.

via An Introduction to Presto — DZone Big Data Zone

Teradata Command Line Tool for Linux

TDSQL.jpg

Been looking for a simple Command line (Terminal) tool for Teradata SQL?  Look no further, this is an option, this article shows the TDSQL tool in use and has the GITHUB Link to it.  It supports Standard Input and Standard Output; it can be used in scripting languages like Bash to automate some tasks.  TDSQL is written in Perl and it also relies on the DBD::Teradata module available here.  There is a little Linux voodoo required here but it is manageable.

 

Business Intelligence – Tableau 9.0 beta rolls out

tableau_preview_new_dragndrop_advancedanalytics-100572699-primary.idge

Tableau is one of the better Business Intelligence tools in the latest NEW BI tools being released that I call BI-Fresh.  It works with many different data sources including some of the larger cloud based databases.  While my experience is limited to Teradata data warehouse with Tableau  I would recommend it for your short list for BI-Fresh evaluations.   Here is a recent review done by ComputerWorld on the latest release 9.0 Beta.

Database .NET from Fish, a Swiss Army Knife tool kit for Database developers

image Teradata, SQL Server, SQLite, MySQL, Oracle, Excel, Access and dBase are databases you may use on a regular basis.  If you do you probably have 3-6 Database tools to help manage them all.  Each of these has their own user interface and capabilities.  Database .NET from Fish Code Library is the one tool to help you manage all these databases and many more.   Database .NET (DBN) is a single EXE file and can be installed as a portable product on a thumb drive or a shared product on Dropbox.

Database .NET is not the best name Fish Code Library could have picked as it makes it difficult to locate it on the web without having to look at a 100 posts about Microsoft’s .NET foundation.

DBN has so many features that DBA’s and DB Developers would like.

Features

Portable
  • Can be run on Corporate computers that don’t have install rights, you folks know who I mean
  • Can be run from a USB Thumb drive
  • Can be run from Dropbox and one instance shared across multiple computers
Free & Premium
  • Free version does 80% of what many users need
  • Premium version is $30…. Really!!  
    That’s less than the cost of a monthly cup of coffee (Cheap coffee at that)
Single EXE
  • Very small footprint
  • No Windows installation required
  • No extra DLL’s that might cause conflicts on your system
  • Updates are simple, just replace the EXE file
  • Overhead is very low, open multiple copies of DBN to work between different servers and database types
  • Fast processing
.NET Data Providers
  • DBN has all the latest .NET drivers embedded in the tool
  • No ODBC Data Source Names to maintain outside the tool
  • Configuration for DB connections maintained by DBN
Teradata
  • Teradata supported with Full capabilities of .NET 15.x.x
Server support
  • 21 databases servers supported
  • Legacy support for dBase and Paradox, anyone remember those?
Export Formats
  • Comma Separated Values (CSV) very common format
  • Tab Separated Values (TSV) gotta love Unix support
  • XML data exchange with bloated tags
  • Excel (XLS) Because if it isn’t a database it’s a spreadsheet
  • Insert SQL portable between different database formats
  • HTML ok the web isn’t a fad and is probably going to be here a while
  • Java Script Object Notation (JSON) NoSQL document format
DOS Command mode
  • DBN  be called in a Windows Batch file
  • Inexpensive and casual ETL tool and data transfer tool
    (GeekMustHave will have an example of Teradata to SQLite Windows Batch file soon.)
  • Database to Excel refresh tool with Windows shortcuts
  • Basic SQL to JSON convertor for relational databases
Version Tracking
  • Version tracking on selected SQL scripts
Code Libraries
  • Simple code library for each database/project in Windows directories and files
  • SQL code can be quickly saved in Windows files in a structured directory
  • Copy all your SQL scripts for all you projects by copy and pasting a single directory
  • Install DBN on Dropbox or other Cloud services and have you DB tool and script everywhere you go.
Support
  • Most Excellent
  • Fish Code Library also has a number of other productivity tools
  • Forum available to post improvements and vote on them
  • Many of the improvement requests show up in the frequent updates
  • Have I mentioned Teradata support

To be a fair reviewer of database tools you do have to mention some areas where the tool could be improved

Improvements

Results Limiter
  • Allows for the number of rows returned in the result to be automatically limited without changing the SQL.  Good for large database queries  and data warehouses usage.
MAC support
  • DBM is a Windows tool, get over it or get a copy of VM Fusion and run it there
  • That being said, a native MAC version would be nice

Teradata Studio, Windows 7, Java 64 Bit runtime

Those who install Teradata Studio on a 64 Bit Version of Windows 7 will need to have to correct version of Java installed.  There are 32 bit and 64 bit versions of Java if you have the incorrect one installed you will get the following error message.

image

If you have installed Java using a browser there is a fairly good chance that you have the 32 bit version installed, not the 64 bit version.

The 64 bit Java JRE version can be found at this link.  After you install this Java version a reboot will be needed in order for the Teradata installer to find the Java path.  As of this date the correct Java version is Version 8 Update 25.  This will change over time.

Teradata Studio – SQL Assistant Display User DB in initial SQL Source Explorer

imageI have been using SQLAssistant for over 20 years back when Teradata systems were called DBC1012’s.   Now I have been asked to put together a class to help developers using SQLAssistant to use Teradata Studio.  So I have just started using it and I’m finding it both fabulous and frustrating.

I have gone through some of the forums and found some very good tips.  There are some users who say Teradata Studio is the future of Teradata developed tools combining SQL Query, Teradata admin and data movement.  It supports large data implementations and JSON.    There are some other Teradata users who claim they will never stop using SQL Assistant even if Teradata decides to stop maintaining it.

One of the items developers may be used to is when they login to SQL Assistant they get the Explorer Panel displaying their home database.  If my user id is SchusterJPWC then I have a database area called SchusterJPWC.  Some Teradata installations give this home or user database some perm space to create work tables or views for projects.  

The default setting for Teradata Studio will not do this unless your user account is owned by the DBC database.  Most Teradata installations don’t put User databases directly under the DBC root.  There is usually some hierarchy and the user database is down 2 or 3 levels.  This means your User database or Home Database will not appear in the SQL Source Explorer window.

You can fix this by going to Windows-> Preferences-> and make the following change,

image

You would think if you checked (All databases and Users) it would include your own user database, it doesn’t if it’s not under the DBA root.  The research Teradata system we use does have all the user databases under the DBC root it (All databases and users) will display my user database.

Determine SQL Server version with a query

I’m working on converting the Adventure Works 2008 database from SQL Server into Teradata so there is a database to experiment with.  Sometimes there is an issue were the SQL Server MDF files for Adventure Works didn’t match the version of the SQL Server instance.   How do you quickly tell all the details of the version, compatibility and other goodies from your server?

Here is a SQL Server query to uncover this information.

This will return a result that looks like this

SNAGHTML1c97f636

image

The Internal Version Number is important, here is a short list of the Major SQL Versions

  • SQL Server Version Compatibility Level Internal Database Version Build
  • SQL Server 2012 RTM 110 706 11.0.2100.60
  • SQL Server 2008 R2 100 665 10.50.xxxx   <—- This matches
  • SQL Server 2008 100 661 10.00.xxxx
  • SQL Server 2005  SP2 90 612 9.00.xxxx
  • SQL Server 2005 90 611 9.00.xxxx
  • SQL Server 2000 80 539 8.00.xxx(x)

Access 2007 / 2010 ADO SQL Server 2008 execute Stored Procedure

I have a passion for writing Access front ends to SQL Server databases.  The same techniques can be used for other databases like Teradata and MySQL.  Much of what you can do in Access with SQL Server can be accomplished with linked tables however there are many time when running a SQL Server Stored Procedure or a Teradata Macro directly is a much better alternative.  Some advance SQL functions like OLAP and processing millions of rows are better done on the industrial servers not on Access direct.  Let the other server do the heavy lifting and just return a nice clean result to your Access application.

For SQL Server connections I highly recommend that you install the Microsoft Native CLI 10 drivers.  Download  This will provide your access with ADO and OLEDB support which in my opinion is the best way to talk direct to SQL Server.  The older DAO is no longer supported with 64 nit implementations of Access which is the majority of my customers.

One of the more difficult points is getting the SQL Server connection string correct for OLEDB and the Native CLI 10 drivers.  In my Access application I set this string once is a Global module that gets called from the AutoExec macro when the application starts up.

Here is an example of my SQL Server connect string for an Access application.  No you can’t have my server name and password.  Smile with tongue out

Here is an example of the code used to call an SP (spPopulateRuleLabel)  which just does some background updating of tables.  It has no parameters and returns no results. 

You will notice the (gsSQLOLEConnect) is used to supply the connection information to the Connection object.

The Stored Procedure (spPopulateRuleLabel) does some background processing and updates tables.  This would have been very difficult to do just using Access and Visual Basic.  The SQL Server TSQL allows for better control over database objects.

Just to give you an idea of what is being done by the SQL Server Stored Procedure here is the code.. Special thanks to Anush.

Teradata and Access – Personal Query Environment

PQE_2000

Download PDF version

I have been a Teradata specialist for quite some time now.  Data Warehousing and tools are in my blood.  Problem is that most data warehousing tools are either too simple requiring the non power user to know SQL or too expensive.  Microsoft Access is a great tool to use to create a what I call a Personal Query Environment or PQE.

Back in 1999-2000 I taught a class on how to use Teradata with Microsoft Access so you can do repetitive queries, reports and data load/extracts.    This class was based on Access 97 and Teradata V2.  Since 1999-2000 a number of other great tools for Teradata have come and some gone.  Recently I was asked to put together an admin utility for a Teradata database that was feeding a web site.  As a result I dug out my old notes, training materials and Access examples.  Looks like I will be updating some of this material including examples of code and I plan to post it here.  The download link above is my original course handouts, boy have things changed.