Counting NULL values in Oracle

Leave a comment

Howdy,
today I had the challenge to count null values in a orale table. At first I tried something like

SELECT
sum(field1 is null) null_counter
FROM
table

but this did not bring be very far.

also:

SELECT
sum(
case field1 
when null then 1
else 0
end
) null_counter
FROM
table

did not get me very far.

After some internet research I came across this neat little thingie:

SELECT
sum(
case nvl(field1,'null') 
when 'null' then 1 
else 0 
end
) null_counter
FROM
table

Re-Post: Julien Hofstede – Pentaho: Increase MySQL output to 80K rows/second in Pentaho Data Integration

Leave a comment

Increase MySQL output to 80K rows/second in Pentaho Data Integration

One of our clients has a MySQL table with around 40M records. To load the table it took around 2,5 hours. When i was watching the statistics of the transformation I noticed that the bottleneck was the write to the database. I was stuck at around 2000 rows/second. You can imagine that it will take a long time to write 40M records at that speed.
I was looking in what way I could improve the speed. There were a couple of options:
  1. Tune MySQL for better performance on Inserts
  2. Use the MySQL Bulk loader step in PDI
  3. Write SQL statements to file with PDI and  read them with mysql-binary

When i discussed this with one of my contacts of Basis06 they faced a similar issue a while ago. He mentioned that speed can be boosted by using some simple JDBC-connection setting.


useServerPrepStmts=false
rewriteBatchedStatements=true
useCompression=true

[[UPDATE 10/2018: In some environments – especially with a high network load iseServerPrepStatements=true is worth a try]]

These options should be entered in PDI at the connection. Double click the connection go to Options and set these values.

Used together, useServerPrepStmts=false and rewriteBatchedStatements=true will “fake” batch inserts on the client. Specifically, the insert statements:


INSERT INTO t (c1,c2) VALUES ('One',1);
INSERT INTO t (c1,c2) VALUES ('Two',2);
INSERT INTO t (c1,c2) VALUES ('Three',3);

will be rewritten into:


INSERT INTO t (c1,c2) VALUES ('One',1),('Two',2),('Three',3);

The third option useCompression=true compresses the traffic between the client and the MySQL server.

Finally I increased the number of copies of the output step to 2 so that there are two treads inserting into the database.

This all together increased the speed to around 84.000 rows a second! WOW!

 

Source: Julien Hofstede – Pentaho: Increase MySQL output to 80K rows/second in Pentaho Data Integration

Oracle Date Territory

Leave a comment

Hi Folks,

I came across the problem that when using something like:

to_char(my_datefield,'D') as dow

to find out the date, this might behave differently on the pentaho production-server, the report designer and (if applicable) an underlying PDI transformation. When connecting to the Oracle-Server, you can click on “advanced” and set the locale to what you need – for example

ALTER SESSION SET NLS_TERRITORY = BELGIUM;

That way, your transformation/report will behave consistently across servers/environment.

 

Cheers

Andre

Handy date calculations in MySQL

Leave a comment

Howdy,

I have been struggeling with date/time calculations for the last couple of years and meanwhile I have quite a collection I would like to share. Note that I have avoided something like date_format(current_date,’%y-%m-01′) because I dont find that very elegant

Simple date calculations

Today

SELECT current_date

Tomorrow

SELECT current_date + interval 1 day

Yesterday (you might guess….)

SELECT current_date - interval 1 day

A week ago

SELECT current_date - interval 1 week

Rather complex date calculations

The first day of last month

SELECT last_day(current_date - interval 2 month) + interval 1 day

The last day of last month

SELECT last_day(current_date - interval 1 month)

The last day of last year

SELECT current_date - INTERVAL DAYOFYEAR(current_date) DAY

the first day of this year

SELECT current_date - INTERVAL DAYOFYEAR(current_date)-1 DAY

last monday

SELECT current_date - INTERVAL weekday(current_date) day

 

If you have more to add, please feel free to put them into the comments and I will happily share them here.

 

Cheers

Andre