Wednesday, July 13, 2016

SqlTableZip is now known as - "SQL Server Query Zipper"

I think names are important. And product names specifically should indicate something about the actual product.

With that in mind, "SQL Server Query Zipper" (or SQL Query Zipper in short) does a better job on explaining what the tool does - even before reading the description.

There's a new version - which in addition to other enhancements, also goes by the new name.

TA-DA!



The console version was also renamed and the executable is now called: SSQZConsole

Tuesday, May 24, 2016

SuperBulkCopy (SBCP) Walk-through

Recently, We've introduced a free new tool called SuperBulkCopy (short name: sbcp)



What it does is simple to explain, and yet pretty challenging to implement - it does everything we wanted BULK INSERT, BCP, or any existing flat file importer to do: Get the job done, as automatic as possible.

In this post I'll provide a walk-through, with many images. Video will also become available in the future but hopefully this segment can get you up to speed as to: "Why do I REALLY need this!"

Again, as mentioned above, this tool is absolutely free. So just get it and start playing!

Let's begin.

Part 1: The struggle


Here's the setup - I got a fairly big file from a client, and need to turn it into a SQL Table for further analysis.

I look at the file:


It's about 1.7gb big. Thinking - already let's have a look with Notepad:


Nah! too big. How about Notepad++?


Nope. The same thing.

OK, next step -- I do know (and we'll see this in a moment) that this file has mixed data types, and everything is wrapped with "quotes". Also - it has headers, which eliminates the ability for either BULK INSERT or BCP to handle it correctly.
So we'll do the next natural thing and jump right into the "Text Import Wizard", or use SSIS directly. (Same engine anyway)



Loaded the file, with the correct delimiters, as well as specified the Text Qualifier - A quick preview indicates we're almost good to go:



Mapping the data into a new table, after also using the "Suggest Data Types" feature




But - OH NO! The process has failed.
This can be caused by so many reasons, from data truncation, into type mismatch, headers not 100% matching the field structure and more.


Looking at the friendly logs:


Fun Times!

Now, I'm not saying that everything fails, and even the ones that do are in some cases solvable after a few iterations of trial & error.

But the question is - WHY BOTHER? Where's there is a free tool today that will do all the hard work for you?

Part 2: The Easy Way Out


Let's try to do that same thing with SuperBulkCopy.
It's very fast and easy to use, so I'll take the time showing a few different methods of getting the job done.

Let's go! (again)

Okay, after installing the tool, let's run it. I'm using the DoccoLabs launcher but you might as well simply run it directly from the start menu.


Okay - now it's loaded.
On Input Settings, I choose that same file. 



Now, even before going further - there's a quick "Preview" option, regardless of file size - it will show you a sample


Yep, Looks great!
Now, there's not a lot to do: Simply defining the Destination SQL Server connection. I'll even ignore the fine-tuning options and simply let it run.
(Note: for the demo I've only imported part of the file, which is another feature, but one can easily import the entire content)


So - completed. Let's go check the table in SSMS:



It's right there. Looking at the table definitions even shows that the best data-types were used:


Basically, we're done.
But I want to spend another minute of showing you how SuperBulkCopy can run just as easily as a console-app from command line.

After setting all the relevant input-output configuration for this demo, we can save the configuration for later use.

In our case, the configuration looks like this:

<?xml version="1.0" encoding="UTF-8" ?>
<config>
    <input>
    <Filename>c:\temp\text_samples\big_file_with_quoted_identifiers_20160301.tsv</Filename>
    <AutoDetectHeaders>True</AutoDetectHeaders>
    <AutoGuessDelimiters>True</AutoGuessDelimiters>
    </input>
    <output>
        <TableName>test1</TableName>
        <OptimizeDataTypes>True</OptimizeDataTypes>
        <ConnectionString>Data Source=127.0.0.1;Initial Catalog=tempdb;Integrated Security=True;</ConnectionString>
        <DropTableIfExists>True</DropTableIfExists>
    </output>
    <AllowAsyncDataWrite>True</AllowAsyncDataWrite>
</config>


And I can save it using the main menu.




Now - I can start using sbcp.exe
Of course, instead of generating the XML file, I could write it myself. Typing sbcp.exe /? provides all the essential information of how to build a configuration file.
Alternatively, everything *can* be done with simple command-line arguments but I find XML option to be more clear and easy to understand.

So, now let's open a command window and direct sbcp.exe to the configuration file:


And here are the results:


Basically - the exact same result as the UI version produces. Only here - the process can be fully automated. (for example, if such file arrives every day and needs to be turned into a table)

Furthermore - this can run directly from SSMS by using the SQLCMD mode. Here's how

Switch into SQLCMD Mode on the SSMS main menu:



Now, run the same as you just did from the command line. don't forget to add the !! indicator.


Part 3: Conclusions

In short, SuperBulkCopy can easily get into flat-file input into your SQL Server. It does a lot such as detecting headers, delimiters, data-types and more.
It's a free tool that can potentially turn this annoying task into a rather easy one.

You can get it from this line: http://www.doccolabs.com/products/superbulkcopy

---

Have anything to add? Let us know! (comments, twitter mentions, emails or whatever channel you like best)





Thursday, May 19, 2016

SQL Stripes is now FREE (Going EOL)



It feels a bit awkward to say it, but SQL Stripes - the first product that ever came out of DoccoLabs, is reaching its End-Of-Life.

As SQL Stripes is getting old, and after a lot of considerations I've decided to not write a new "similar" product mostly due to the ROI given a growing number of other monitoring tools out there.

SQL Stripes, like all of the other products was written with love and passion to making the SQL Server users' life easier.
It was written out of need, and served many customers. (And still does to this day)

For existing users who get what they need from the product - feel free to working with this but keep in mind that there will be no more future releases, fixes or any sort of support around it.

In addition, We're making SQL Stripes free! If you would like to keep working with it after the trial is over (or even before it expires), send me an email and I'll send you an activation key.

SQL Stripes Screenshot

Monday, February 22, 2016

Introduction to SQL Table Zip *Console*


The latest version of our Query-Level Export/Backup App, "SQL Table Zip", has a brand new feature - A stand-alone console version, allowing you to Import & Export any query from your command line / powershell / directly from SSMS.

Here is a simple end-to-end walkthrough with the console version:

After installing the latest version, your destination folder has a new file named SqlTableZipConsole.exe


To make it easy to access, the setup process, by default, has a new checkbox for adding the installation folder to your environment variables path. This means that you can type SqlTableZipConsole (or SqlTableZip) from anywhere in your command line. An indication that it works would be to simply open a 'cmd' window and type in SqlTableZipConsole like in this screenshot:


From this point on, we'll be using SSMS for the demo, although it can be done anywhere.

The basic argument that SqlTableZipConsole takes is /ConfigFile:\path\to\MyConfigFile.xml

Here's a sample BACKUP config file, notice the <action> tag and the following properties:



\temp\demo\backup_config_demo.xml
<?xml version="1.0" encoding="utf-8" ?>
<config>
  <Action>Backup</Action>
  <Query>SELECT * FROM Production.WorkOrder</Query>
  <BackupFile>c:\temp\ProdWorkOrder.sqz</BackupFile>
  <BackupConnectionString>Data Source=127.0.0.1;Initial Catalog=AdventureWorks2014;Trusted_Connection=True</BackupConnectionString>
</config>


Next, let's see how we run everything inside SQL Server Management Studio (SSMS)

First thing - switch SSMS to SQLCMD mode:


Next, let's run a simple backup command based on the configuration file above. In SSMS the syntax will look like this:


Let's explain the results: The query result is now stored in ProdWorkOrder.sqz (SqlTableZip format) and has been compressed from the original ~16mb to ~0.5mb! That's a lot.

Now let's see how we restore this file back to a table. For the demo we'll restore the file to a different database under a different table name.

Here's the Restore's configuration file:


\temp\demo\restore_config_demo.xml
<?xml version="1.0" encoding="utf-8" ?>
<config>
  <Action>Restore</Action>
  <RestoreFile>c:\temp\ProdWorkOrder.sqz</RestoreFile>
  <RestoreTableName>MyNewTable</RestoreTableName>
  <RestoreConnectionString>Data Source=127.0.0.1;Initial Catalog=Sandbox;Trusted_Connection=True</RestoreConnectionString>
</config>


Note the different table name provided <RestoreTableName> as well as the different database name in the <RestoreConnectionString> tag.

Now, we'll point SqlTableZipConsole to this configuration file and run:


That's the entire end-to-end operation in a nutshell! Here's a simple select from the new created table:




One final note:

While SqlTableZip is not a free software, the restore operation is. This means that even with no purchased license, restoring a previously backed-up files is always possible.


SQL Table Zip Logo

Wednesday, November 18, 2015

The DBA’s guide to the [new] galaxy: Part III

| Part 3: Use the force, Luke!



Choosing the right architecture --

Experience with a wide variety of data stores doesn’t only help with choosing the right architecture, but also being confident about vetoing bad designs.
This goes both ways; so for the following (intentionally over-exaggerated) scenarios -

Scenario 1:
Requirement:
A new service which keeps track of your company’s orders.
Data specs: there are supposed to be about 100 orders per day, the information must be instantly consistent. Orders relate to products and customers.
Occasionally, some ad-hoc reports are needed, mostly to aggregate orders per customers, products or date range.
Selected solution:
the development team decided to save the orders to Redis, then use a queue to transform the data to an HDFS store,
out of which some map-reduce jobs will be used to return the relevant data.

So yes, the dev team got their hands all over a new trending technologies, and they can add “NoSQL” and “BIG”-data, but in fact, this is a very inappropriate solution for the problem.


Here’s another example -

Scenario 2:
Requirement:
Track the company app’s site visitor interaction in real-time on a heat map. In addition, show geo-locations trend over time.
Data specs: the app is extremely popular, gaining ~2500 interaction per second
Purposed solution:
Use a relational database on a very strong, high-end machine to able to support the 2500/sec OLTP rates
Create additional jobs running every few minutes, in order to aggregate the time frames & performing IP lookups.

Again, although this might work, this is again not a preferred solution.
In this case a real scale-out architecture will perform better at a lower cost.
A typical ELK stack for example could handle this scenario more naturally.

Can you see where this is going? Knowledge is important to divert the architecture in the right way.
Different specs require different platform implementation and in many cases - a mix.

Inappropriate platform selection can come from many sources, including:

  • Developers who want to experience with different technologies
  • PMs/POs lacking the detailed knowledge
  • CTOs wishing to brag about using some new technology (preferably while playing golf with other CTOs)
Choosing the right platform for a given solution is extremely important, even when the recommended technology is not the trending one.

| Epilogue

The world of data is constantly changing. The variety of solutions for storing & retrieving data in various shapes and sizes is rapidly growing.
Relational databases are strong, and will stay strong - for storing and retrieving relational data, usually when no scale-out is required. For all the other types - the world has already changed.
If there’s one thing you should take away with you, is that looking forward - you should embrace these new technologies, get to know them and understand the architectural position of the dominant ones.


Links to Part 1, Part 2 

Wednesday, November 11, 2015

How ClipTable works?

A few days ago I was approached by someone at work, who got some files needed to be exported into one of our SQL Servers.
The file format wasn't very intuitive, and caused this coworker a lot of different errors trying to use the "Import and Export Wizard". (Which is a basic SSIS task behind the scene)

Anyway, after being asked to "give it a shot", I naturally went to ClipTable, and of course, got this done in less than 2 minutes.

So this coworker asked me how this was done so fast, and what he was doing wrong.
I've explained that I was using a different tool. (and explained a bit about ClipTable)

"Yeah, but how was ClipTable able to determine how to turn the file into a table without any configuration?" she asked.

So, I answered and decided to write this post about some behind-the-scene ClipTable techniques.

If you're new to the tool -
ClipTable is a very fast SQL Import tool, which allows you to turn almost any tabular data into a SQL Server/Oracle/MySQL/PostgreSQL/Firebird Table.

Here is a behind-the-scenes glimpse to ClipTable's engine (that could) and some of the things it's able to do:


So, when you import from memory (clipboard) or file, here is a partly list of what's happening:

[The engine analyzes the data, answering the following questions]

  • Is this an XML string?
    • Can this turn into a 2-d table?
  • Is this a JSON data?
    • Is this a serial JSON data? (repeated columns)
    • Which parts of the data can turn into a 2-d table?
  • Is this a textual data?
    • Does the data seem valid?
    • Analyzing the string pattern,
      what is most likely to be the column/row delimiters?
    • Does the data include headers?
    • Are there white-spaces to be removed?
  • Are there any duplicate field names?
  • What is the best Data Type for each column?
  • What's the fastest method to write the table into the destination server?
    • Does this server support Bulk Copy/Insert?
    • Can insert statement sent in a big batch?
These engine features help ClipTable turning this:





Into this:


Into this:


Into this:


In just a few seconds.

Wednesday, November 4, 2015

SQL ClipTable now offers better JSON handling and SQL Server 2016 support


There's a new version of SQL ClipTable released today, which has multiple engine updates as well as two important features:

  • SQL Server 2016 support (tested against CTP's, will make sure RTM is fully supported upon release)
  • JSON format support has been extended to support a 'serial' JSON file, which is basically a file on which each line represents a new JSON-based row, with a similar schema.
As a reminder, ClipTable lets you import almost any data, of any shape and form into your database. Supporting all major relational database engines available today, including SQL Server, Oracle, MySQL, PostgreSQL, FireBird and more.


Want to see the extended JSON support in action?
Here's an example for such a file: Try Copy the code below & Paste into ClipTable

{"accept_lang":"hr-HR","object_id":"149987216","object_pos":1,"client_ip":"212.92.194.156","correlation_id":"0AF0010A-B638_AC110006-0050_560B25D6_1FDF5E8-0001","event_ids":["496538287"],"gallery_object_count":15,"gallery_title":"Jimmy Page","gallery_type":"personality","gallery_type_id":"208663","geo":{"city_name":"","continent_code":"EU","coordinates":[15.5,45.1667],"country_code2":"HR","latitude":45.1667,"longitude":15.5,"timezone":"Europe/Zagreb"},"has_ads":true,"referer":"http://www.google.hr/url?sa=i","signal_brand":"dl","signal_event":"object Viewed","signal_source":"dl","spectrum":"dl-prod-producer-51","timestamp":"2015-09-30T00:00:03Z","url":"http://www.retrofile.com/detail/news-photo/","user_agent":"Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko","user_id":"0","visitor_id":"5bf0ba11-2373-44c0-b4d3-b0ccad75f4fa"}
{"accept_lang":"en-US,en;q=0.5","object_id":"160011161","object_metadata":{"object_family":"Creative","object_type":"image","editorial_products":[],"has_nudity":false},"client_ip":"12.204.91.250","correlation_id":"0AF0010A-E611_AC110006-0050_560B25ED_1FE07C4-0001","geo":{"city_name":"Riverside","continent_code":"NA","coordinates":[-117.4031,33.9059],"country_code2":"US","latitude":33.9059,"longitude":-117.4031,"timezone":"America/Los_Angeles"},"queried_keywords_by_clause":null,"referer":"http://www.doccolabs.com/","signal_brand":"dl","signal_event":"object Details Viewed","signal_source":"dl","spectrum":"dl-prod-producer-51","timestamp":"2015-09-30T00:00:02Z","url":"http://www.doccolabs.com/blog","user_agent":"Mozilla/5.0 (Windows NT 6.1; WOW64; rv:38.0) Gecko/20100101 Firefox/38.0","user_id":"0","visitor_id":"0a67788b-0022-455c-85b3-6e420787a069"}
{"accept_lang":"en-GB","ads_viewed":[8.2626951421e+10],"object_id":"175554847","object_pos":12,"client_ip":"81.108.221.204","correlation_id":"0AF0010A-C299_AC110006-0050_560B25E6_200D348-0001","event_ids":["526528765"],"gallery_object_count":15,"gallery_title":"Una Healy","gallery_type":"personality","gallery_type_id":"5523039","geo":{"city_name":"Royal Leamington Spa","continent_code":"EU","coordinates":[-1.5047000000000001,52.3123],"country_code2":"GB","latitude":52.3123,"longitude":-1.5047000000000001,"timezone":"Europe/London"},"has_ads":true,"referer":"http://www.doccolabs.com/purchase/","signal_brand":"dl","signal_event":"object Viewed","signal_source":"dl","spectrum":"dl-prod-producer-51","timestamp":"2015-09-30T00:00:01Z","url":"http://www.doccolabs.com/purchase/","user_agent":"Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko","user_id":"0","visitor_id":"c902ed6c-96ff-4778-a85e-ca9fc7a9dd13"}
{"accept_lang":"en-US,en;q=0.8","object_id":"112027935","object_pos":0,"client_ip":"108.54.239.65","correlation_id":"0AF0010A-0B19_AC110006-0050_560B25F5_20070C2-0001","event_ids":["75330148"],"gallery_object_count":15,"gallery_title":"Charmed Celebrates 150 Episodes and First Season on DVD","gallery_type":"event","gallery_type_id":"75330148","geo":{"city_name":"Jamaica","continent_code":"NA","coordinates":[-73.7939,40.7148],"country_code2":"US","latitude":40.7148,"longitude":-73.7939,"timezone":"America/New_York"},"has_ads":true,"referer":"http://www.google.com/","signal_brand":"dl","signal_event":"object Viewed","signal_source":"dl","spectrum":"dl-prod-producer-51","timestamp":"2015-09-30T00:00:01Z","url":"http://www.google.com/flights","user_agent":"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/45.0.2454.101 Safari/537.36","user_id":"0","visitor_id":"44e8d178-4022-4495-9ea7-412e797ae96b"}
{"accept_lang":"hr-HR","object_id":"149987216","object_pos":1,"client_ip":"212.92.194.156","correlation_id":"0AF0010A-B638_AC110006-0050_560B25D6_1FDF5E8-0001","event_ids":["496538287"],"gallery_object_count":15,"gallery_title":"Jimmy Page","gallery_type":"personality","gallery_type_id":"208663","geo":{"city_name":"","continent_code":"EU","coordinates":[15.5,45.1667],"country_code2":"HR","latitude":45.1667,"longitude":15.5,"timezone":"Europe/Zagreb"},"has_ads":true,"referer":"http://www.google.hr/url?sa=i","signal_brand":"dl","signal_event":"object Viewed","signal_source":"dl","spectrum":"dl-prod-producer-51","timestamp":"2015-09-30T00:00:03Z","url":"http://www.retrofile.com/detail/news-photo/","user_agent":"Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko","user_id":"0","visitor_id":"5bf0ba11-2373-44c0-b4d3-b0ccad75f4fa"}
{"accept_lang":"en-us","client_ip":"107.220.186.143","correlation_id":"0AF0010A-F236_AC110006-0050_56297879_115B0572-0001","gallery":{"object_count":8,"related_celeb":0,"title":"46th Mt. San Antonio College Relays - April 18, 2004","type":"event","type_id":"75059348"},"geo":{"city_name":"Cumming","continent_code":"NA","coordinates":[-84.1404,34.1483],"country_code2":"US","latitude":34.1483,"longitude":-84.1404,"timezone":"America/New_York"},"referer":"https://www.google.com/","session_id":"276095c4fc","signal_brand":"dl","signal_event":"Gallery Viewed","signal_source":"dl","spectrum":"dl-prod-producer-52","timestamp":"2015-10-23T00:00:00Z","url":"http://www.doccolabs.com/","user_agent":"Mozilla/5.0 (iPhone; CPU iPhone OS 9_0_2 like Mac OS X) Safari/601.1","user_id":"0","visitor_id":"81ebb423-bda9-4707-b897-7cc86d58de91"}
{"accept_lang":"","client_ip":"81.158.98.51","correlation_id":"0AF0010A-1680_AC110006-0050_56297882_115B0C02-0001","gallery":{"object_count":15,"related_celeb":0,"title":"Prince Harry Visits Nottingham","type":"event","type_id":"166614266"},"geo":{"city_name":"Dundee","continent_code":"EU","coordinates":[-2.9766,56.461],"country_code2":"GB","latitude":56.461,"longitude":-2.9766,"timezone":"Europe/London"},"referer":"http://www.google.com/search","session_id":"9f1bcbe265","signal_brand":"dl","signal_event":"Gallery Viewed","signal_source":"dl","spectrum":"dl-prod-producer-52","timestamp":"2015-10-23T00:00:03Z","url":"http://www.doccolabs.com/detail/news-photo/prince-harry-signs-his-name-with-a-paint-pen-on-a-large-news-photo/167504061","user_agent":"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko; Google Web Preview) Chrome/27.0.1453 Safari/537.36","user_id":"0","visitor_id":"1e65bdc1-7862-438c-99e5-55a0d89b7cbd"}
{"accept_lang":"","client_ip":"173.88.159.62","correlation_id":"0AF0010A-3757_AC110006-0050_56297881_1148FB3A-0001","gallery":{"object_count":15,"related_celeb":0,"title":"Sports Contributor Archive 2015","type":"event","type_id":"530880577"},"geo":{"city_name":"North Ridgeville","continent_code":"NA","coordinates":[-82.019,41.3895],"country_code2":"US","latitude":41.3895,"longitude":-82.019,"timezone":"America/New_York"},"referer":"http://www.google.com/search","session_id":"7125b47d91","signal_brand":"dl","signal_event":"Gallery Viewed","signal_source":"dl","spectrum":"dl-prod-producer-52","timestamp":"2015-10-23T00:00:02Z","url":"http://www.doccolabs.co.uk/detail/news-photo/andre-mcgee-of-the-louisville-cardinals-in-action-during-a-news-photo/493431424","user_agent":"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko; Google Web Preview) Chrome/27.0.1453 Safari/537.36","user_id":"0","visitor_id":"fc049f9e-22c0-408f-8466-fa4ee376ba10"}