Messages posted by: Venkat_B

Hi everyone,

I'm using PostgresPlus 8.3.3. Here i'm having a problem while loading data.

The main problem is while loading using copy command if one row violates then its just throwing the error and comming out of loading instead of ignoring the bad data.

So for this i'm using pgloader 1.1

The second is i want to add extra column to get inserted which is not in csv along with loading.

Is there any way to load the data in to the table and is there any other option for loading data skipping the bad records.

Support for these two issues is really great helpful for me to move forward.

Thanks,
Venkat.

Thanks Mason,

Also i'm having one more problem , i.e., some bad data is coming along with my csv file. Is there any alternate method for PostgresPlus 8.3 where i can load the perfect data and wirte bad data to some file.

Thanks,
Venkat

Hi,

I'm having a column with timestamp datatype and in my csv file the format is in the following format: 21-DEC-07 12.00.00.000 AM

Is there any possible way to change the date format and timestamp format while loading the data using COPY command:

Here is the following error i'm facing with:

test=# create table t1(col1 timestamp);
CREATE TABLE

test=# insert into t1 values('21-DEC-07 12.00.00.000 AM');
ERROR: invalid input syntax for type timestamp: "21-DEC-07 12.00.00.000 AM"

test=# select current_timestamp;
now
--------------------------------
04/09/2008 12:07:26.335413 IST

Quick response can help me to move forward.

Thanks,
Venkat

Hi Mason,

Thanks for the reply,

But the table containing only 5 to 10 rows in a table which is non partitioned also taking lots of time and finally there is no result.
Is there any place like log file to check where it is getting choked.

Thanks

Hi,

Truncating in GridSQL is taking lot of time even for very minimum number of rows.

Thanx

Hi Mason,
We are testing GridSQL with Postgresplus. When I issue the below query I am getting incorrect result..

GridSQL -> select calledpartynumber from test_cdr
GridSQL -> where substr(calledpartynumber,1,2) not in ('11','12');
+-------------------+
| calledpartynumber |
+-------------------+
| 1194775588345 |
| 12456 |
+-------------------+
2 row(s).

Whereas, the below query also gives the same result..

GridSQL -> select calledpartynumber from test_cdr
GridSQL -> where substr(calledpartynumber,1,2) in ('11','12');
+-------------------+
| calledpartynumber |
+-------------------+
| 1194775588345 |
| 12456 |
+-------------------+
2 row(s).

And another is when giving limit and offset in a single query is resulting no rows even there are rows in table.

Thanks,
Venkat

Hi Mason,

Even if I create table with XROWID i'm getting the same problem.

Thanks
Venkat

Thanks Mason,

Mason we are using postgres 8.3,
And here we are having the problem and by using null '' in copy command the problem disappears.

still i'll cross check once again.

Thanks

Hi

When i'm using serial/biserial datatype and loading data through gridsql i'm getting error like

SQLException: ERROR: java.lang.NumberFormatException: For input string: "DL"

the table is:

create table test(col1 integer,col2 varchar,col3 varchar,col4 numeric,col5 varchar,col6 bigserial);

copy test(col1,col2,col3,col4,col5) from '/opt/test1.csv' delimiter ',' CSV;

And here i'm getting the error.

Thanks
Venkat

Mason thanks for reply,

Mason, the sample create works fine and loading also worked fine. Here when we generate to csv the null values like \N but in my CSV its blank

I placed my statement and sample data which i'm running

GridSQL -> CREATE TABLE sample (
GridSQL -> type INTEGER,
GridSQL -> other VARCHAR(50),
GridSQL -> category INTEGER,
GridSQL -> start_date date,
GridSQL -> start_time VARCHAR(10),
GridSQL -> end_date date,
GridSQL -> end_time VARCHAR(10),
GridSQL -> duration INTEGER,
GridSQL -> unit INTEGER,
GridSQL -> chargeable_duration NUMERIC(7,2),
GridSQL -> unit_chargeable_duration INTEGER,
GridSQL -> file_name VARCHAR(50),
GridSQL -> cui INTEGER
GridSQL -> ) partitioning key file_name on ALL;
OK

GridSQL -> CREATE TABLE sample_010807 (CHECK(start_date=DATE '2007-08-01')) INHERITS (sample);
OK

GridSQL -> copy sample_010807 from '/opt4/sample.csv' delimiter ',' CSV;
SQLException: ERROR: invalid input syntax for integer: ""

[postgres@localhost ~]$ head -25 sample.csv
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,21.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,31.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,774.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,77.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
5,,,2007-08-01,141859,2007-08-01,141859,,,50.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1
2,,,2007-08-01,141859,2007-08-01,141859,,,60.00,1,5867,1

And also Mason one more serious problem i'm facing is

org.postgresql.util.PSQLException: ERROR: Server has aborted execution, cause is: java.lang.IllegalArgumentException : port out of range:-1

org.postgresql.util.PSQLException: ERROR: Server has aborted execution, cause is: java.nio.channels.ClosedChannelException : null

org.postgresql.util.PSQLException: ERROR: Server has aborted execution, cause is: java.net.ConnectException : Connection refused

These are the exceptions which i'm facing while running query. But always after restarting the server the problem disappears and again after few transactions the same problem arises

Can you support me with these issues.....

Thanks
Venkat

Mason Thanks for reply,

Mason no i'm using default port (6453) only. I have not changed the configuration and the same was working fine before.

But now while firing query on the table then i'm facing this error.

Thanx
Venkat

yes Mason thanx for reply,

Mason i restarted the gridsql server and all nodes servers then that worked fine...... I hope one of the server nodes not got contacted correctly so i had the error.

But Mason i'm having one more problem i.e., when loading a data from csv file using copy command i'm getting error like

ERROR: invalid input syntax for integer: ""

There in CSV that particular column is null and in table creation i have not mentioned not null to that column. That column is also not a DATE type column

I'm not getting the exact problem, can you suggest what all i need to check for such kind of error.....

Thnx
Venkat

Can anyone suggest what exactly the error means:

SQLException: ERROR: Server has aborted execution, cause is: java.lang.IllegalArgumentException : port out of range:-1

and what all i need to check to resolve the error.

Thnx
Venkat

Hi,

when i'm creating a view from console it is running fine but when running through jdbc i'm getting error.

I'll just give you the sample code which we are running.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.*;
public class scrap {
public static void main(String[] args) {

try{
Class.forName("com.edb.Driver");
com.edb.jdbc3.Jdbc3Connection con = (com.edb.jdbc3.Jdbc3Connection)DriverManager.getConnection("jdbc:edb://127.0.0.1:6453/xtest","admin","secret");
String sql="create view CUBE_SHR25_VIEW as select to_char(start_date,'yyyy-mm-dd') start_date," +
"call_type,subscriber_type, gid,duration,count from CUBE_SHR1 where gid=0";

Statement stmt=con.createStatement();

boolean i=stmt.execute(sql);

System.out.println("View result "+i);
}
catch(Exception ex){
ex.printStackTrace();
}
}

}

the result is : View result false

and when i'm seeing it from console giving the following statement the output is as follows:

GridSQL -> describe CUBE_SHR25_VIEW;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| VIEW_TEXT | VIEW_COLUMN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| select to_char(start_date,'yyyy-mm-dd') start_date,call_type,subscriber_type, gid,duration,count from CUBE_SHR1 where gid= | start_date, call_type, subscriber_type, gid, duration, count |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s).

here in output i clearly gave gid=0 but it is just showing gid=

And in second case i just modified the query to

="create view CUBE_SHR50_VIEW (start_date,call_type,subscriber_type,gid,duration,count)as select to_char(start_date,'yyyy-mm-dd') start_date," +
"call_type,subscriber_type, gid,duration,count from CUBE_SHR1 where gid=0";

and here the output is :

GridSQL -> describe CUBE_SHR50_VIEW;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| VIEW_TEXT | VIEW_COLUMN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT to_char( "cube_shr1"."start_date", 'yyyy-mm-dd') as "start_date", "cube_shr1"."call_type" as "call_type", "cube_shr1"."subscriber_type" as "subscriber_type", "cube_shr1"."gid" as "gid", "cube_shr1"."duration" as "duration", "cube_shr1"."count" as "count" From "cube_shr1" WHERE ("cube_shr1"."gid" = 0) | start_date, call_type, subscriber_type, gid, duration, count |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s).

here is showing gid=0 but when querying it gives the following error:

GridSQL -> select * from CUBE_SHR50_VIEW;
SQLException: ERROR: Server has aborted execution, cause is: java.lang.IllegalArgumentException : port out of range:-1

I'm not getting the problem....

Hello,

I'm using PostgreSQL Advanced Server 8.3

And the parameter in postgres.config for datestyle = 'Postgres,European'
I am loading data into table using copy command "copy table1 from '/opt/sample.csv' delimiter ',' CSV;" where one of the column in table is date datatype. And the format of that column in csv file is ddmmyyyy and now i want the same to get loaded into the table. How can i make this. Is there any parameters or can i change it while loading using to_char or something?

If any detailed info can help me!

Thanks
Venkat.

Hi,
when executing the following query in GridSQL i'm getting below error, can anyone suggest what went wrong

GridSQL -> select count(*) from TDR_MSC_060807;
SQLException: ERROR: Failed To Get Results For ( SQL , NodeURL) : ( SELECT COUNT(*) AS XCOL1 FROM TDR_MSC_060807 ) eQS Node 3 has aborted execution, cause is: com.edb.gridsql.exception.XDBServerException : Can not send data to Nodes

Thankx
Venkat

Thank u Mason,

In between even i'll check with round robin option.

regards

Yes Mason here are the partition key details:

GridSQL -> show tables;
+-------------------------------------------------------------------+
| table | table_partitioning_column | table_nodes |
+-------------------------------------------------------------------+
| CUBE_AGG_MSC | SUBSCRIBER_TYPE | 2,4,9,8,6,1,3,10,7,5 |
| CUBE_MSC_10 | SUBSCRIBER_TYPE | 2,4,9,8,6,1,3,10,7,5 |
| TDR_MSC_060807 | HOUR | 2,4,9,8,6,1,3,10,7,5 |
| TDR_MSC_HOUR | HOUR | 2,4,9,8,6,1,3,10,7,5 |
+-------------------------------------------------------------------+
4 row(s).

For the table TDR_MSC_HOUR we are mentioning the partition key as hour and inheriting TDR_MSC_HOUR to TDR_MSC_060807 and check constraint with start_date. And the tablespace with 10 locations on 10 different logical nodes which are on 10 different disks also assigned to TDR_MSC_HOUR.
And it is considering subscriber_type as the partitioning key for the cube table. And it not with round robin option, even i'll test with that option and check the results.

I hope i have provided the required information, Please let me know if any other information is required.

Thanku Mason for the reply,

can you help me how to run an sql file from GridSQL,
and how to set time on to know the query time.

And one more is
i queried the following query in oracle and GridSQL with 10 nodes in GridSQL and with 8 partitions on different disks in oracle.Here the partitioning key is Hour on both the databases.Can i know why is it taking so much of time and can you guide me what are the steps i need to follow to reduce the time with GridSQL, which tuning concepts i need to consider to reduce the time.

In Oracle:
CREATE TABLE CUBE_MSC_HOUR
(START_DATE DATE,SUBSCRIBER_TYPE INTEGER,CALL_TYPE INTEGER,SERVED_IMSI VARCHAR2(100),COUNTRY INTEGER,
IN_TRUNK VARCHAR2(20),OUT_TRUNK VARCHAR2(20),SOURCE_ID VARCHAR2(20),TRUNK_TYPE INTEGER,IUC INTEGER,
DURATION INTEGER,TDRCOUNT INTEGER);

Table created.

Elapsed: 00:00:00.40
SQL> ALTER TABLE CUBE_MSC_HOUR ADD GID INT;

Table altered.

Elapsed: 00:00:00.22
INSERT INTO CUBE_MSC_HOUR select START_DATE,SUBSCRIBER_TYPE,CALL_TYPE,SERVED_IMSI,COUNTRY,IN_TRUNK,OUT_TRUNK,
SOURCE_ID,TRUNK_TYPE,IUC,sum(DURATION),COUNT(*),0 From MSC_PAR_HOUR
where START_DATE between '05-AUG-07' and '05-AUG-07'
group by START_DATE,SUBSCRIBER_TYPE,CALL_TYPE,SERVED_IMSI,COUNTRY,IN_TRUNK,OUT_TRUNK,SOURCE_ID,TRUNK_TYPE,IUC;

14636239 rows created.

Elapsed: (very fast time)
SQL>

In GridSQL:

GridSQL -> CREATE TABLE CUBE_MSC_10(START_DATE DATE,SUBSCRIBER_TYPE INTEGER,CALL_TYPE INTEGER,SERVED_IMSI VARCHAR(100),COUNTRY INTEGER,IN_TRUNK VARCHAR(20),OUT_TRUNK VARCHAR(20),SOURCE_ID VARCHAR(20),TRUNK_TYPE INTEGER,IUC INTEGER,DURATION INTEGER,TDRCOUNT INTEGER);
OK

GridSQL -> ALTER TABLE CUBE_MSC_10 ADD GID INT;
OK

GridSQL -> exit;
-bash-3.2$ date
Tue Jun 17 19:10:17 IST 2008
-bash-3.2$ ./gs-cmdline.sh -d TEST -u admin -p secret

GridSQL -> INSERT INTO CUBE_MSC_10 select START_DATE,SUBSCRIBER_TYPE,CALL_TYPE,SERVED_IMSI,COUNTRY,IN_TRUNK,OUT_TRUNK,
GridSQL -> SOURCE_ID,TRUNK_TYPE,IUC,sum(DURATION),COUNT(*),0 From TDR_MSC_060807
GridSQL -> where START_DATE between '2007-08-06' and '2007-08-06'
GridSQL -> group by START_DATE,SUBSCRIBER_TYPE,CALL_TYPE,SERVED_IMSI,COUNTRY,IN_TRUNK,OUT_TRUNK,SOURCE_ID,TRUNK_TYPE,IUC;
14640524 row(s) affected

GridSQL -> exit;
date
-bash-3.2$ date
Tue Jun 17 20:25:27 IST 2008
-bash-3.2$
Total Time it took is 1Hr 15 Min

Hi everyone,

Can anyone support me how to create a cube in GridSQL with different dimensions. Is there any function like cube or rollup in GridSQL or we need to follow any other format.

I have gone through maximum docs but i didn't found solution.

If it is like we need to create it manually then how can we give bind variables in insert statement. Syntax and examples can help me more.

Thanx in advance!