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"}