Saturday, June 11, 2016

Want to save kettle Logging in database?

However, when you execute job using spoon it produce the logs in UI, the same logs you can also write in database. Here is article how we can write the logs generated during execution in the database. See below:

We can easily save the logs generated by the kettle in database tables. Easiest ways is to change the properties of the job.

Create table in database level. I am using SQLServer in my case and field used in below table are required field.
We have to consider all field while creation of table.

Create table logging
(
 id_job int
, channel_id varchar(100)
, jobname  varchar(100)
, status varchar(100)
, lines_Read int
, lines_written int
, lines_updated int
, errors int
, startdate datetime
, enddate datetime
, logdate datetime
, depdate datetime
, replaydate datetime
, log_field varchar(max)
);

Now create a job in kettle.
Double click on job (open area) - Job Properties - Log (tab)




Remember that the number of field selected using checkbox in Log_Table_Fields dialogue box, should exists in tables.
In the add-on you can create n number of fields (depend upon the limit of table and database) in same table

Tuesday, June 7, 2016

database url and driver

JDBC drive used to connect between application or database or in many cases as well. such as Any third party tool or application where you want to pull out data. To do this we used JDBC URL/ class paths.

Every server have their own JDBC drivers and url, class paths. URL you need to put in you application where connections needs to define. Also every server have their own jdbc***.jar files, you need to download and place these jar files in lib folder of repository folder to make connection happen and live. So that you can read, edit, delete, the data in database.

sqlserver 
database url
jdbc:sqlserver://192.168.1.1:1433;databaseName=XYZ-DatabaseName


spago bi
url - jdbc:sqlserver://192.168.1.1
driver - com.microsoft.sqlserver.jdbc.SQLServerDriver


jtds driver
database url - jdbc:jtds:sqlserver://192.168.1.1:1433/XYZ-DatabaseName


oracle
url - jdbc:oracle:thin:@localhost:1531:XYZ-DatabaseName
driver - oracle.jdbc.driver.OracleDriver

Sunday, June 5, 2016

Playing With SQL Quries


Do you want to play with SQL queries, what a disgusting question, no this is not disgusting, yes you can play, even create games using various codes. Lets create a query to create a triangle. Below SQL query is prepare in Oracle SQL, it can clear your logical skills and usage of SQL function at right place and at right time.


SQL> select lpad(' ',8-level, ' ') ||rpad('*',level-2, '*')
  2  || rpad('*',level-1, '*')
  3   as  a
  4  from dual connect by level < 8
  5  order by level
  6  /

A
---------------

      *
     ***
    *****
   *******
  *********
 ***********

7 rows selected.


web stats