Useful Impala commands that you can use to improve queries performance are:
COMPUTE STATS
SET MEM_LIMIT
CREATING TEMP TABLE
Those statements will make your code smarter.
Performance will be greater and your managers, users and DB’s will be happy to work with you.
INSERT OVERWRITE – will make your life much easier when handling deletes.
Continue reading to know when it is good to apply those statements.
Read MoreYou have a big table. The biggest in your system.
You may say big table, big fun but also in some situation a big challenge.
The manager gives you a task:
How would you approach this task?
What query will you build? How would you minimize logical reads? Would you approach this task differently if it was a one time activity or task executed on a regular basis?
Consider: is this table used exclusively used by you? Maybe in parallel, some other process execute inserts into this table?
Continue reading to see how to delete data in batch on SQL Server.
Read MoreRemoving duplicates, is a challenging task.
Sometimes you need something special. Using DISTINCT/ GROUP BY / UNION is not enough.
You need to remove duplicates is some other way: using window function:
You can do it using following query:
If you would like to see how input data looks like. What is the expected result. When this approach might not be a good idea. Please continue reading.
Read MoreWe all work with data.
Amount of data is growing fast, in the business setup or daily life. There is a need to extract them from different places, marge it, filter and send it to someone.
And do it AS FAST AS POSSIBLE.
Probably you also have a lot of data to be analyzed.
Most likely, you don’t like to repeat this operations over and over again. Doing everything manually is a tedious task.
Python and pandas might be the tools that you need.
Pandas gives you possibility to:
– read it from heterogeneous data sources: (CSV, Excel, Database, Parquet etc)
– analyze the data,
– operate on a data,
– manipulate the data,
– supplement it with another data,
– filter and sort.
After you are done with your operations, pandas gives you a possibility to store it in your favorite format: Excel, CSV, Parquet. Whatever you like.
Read More
Imagine that it is your first day in new company. Your manager is giving you an ambitious plan of restructuring the current Data Warehouse design.
He said that it is probably not a task for this week, but in 3 months he would like to have some proposal from you. He points some people that you might talk to.
You would like to make this process efficient. Imagine a set of questions that you could as to help you solve this puzzle.
How to create a DWH inventory? How to start? What to look for? What are the red flags?
Time is already ticking.
Let’s have a good checklist for Staging layer at the beginning.
Read More
PowerShell is a powerfull tool that will make your life easier. You can use it to automate your daily work or make boring taks interesting. It can also save you time to do whatherever you like.
If you are working on a Windows machine you already have it installed. This is an additional benefit.
As a bonus please find a git scripts at the end of the article. There are two working programs that are doing all the juicy stuff.
In this blog post you will see how can you:
– Connect to Excel using PowerShell
– Get a sheet name
– Find a named table
– Display value from the cell
– Loop through table
– Execute Excel Macro from PowerShell
– And finally save an Excel file using PowerShell