Re: [luciddb-users] LucidDB for Spatial OLAP

[Emily G. <eg...@re...>]

All,

I've been testing out the LucidDB instance for the project Paul has described.  I went through the 
ETL tutorial and got all the examples to work.  And then I moved on to trying to get some data 
loaded from our PostgreSQL 8.1.1 database loaded into the Lucid environment and have run into a few 
issues.

1.  The first challenge I came across was that Postgres usually names everything in lower case. 
However the Lucid interface (or maybe this is a jdbc thing) converted everything to upper case.  So 
when I did a

import foreign schema habc
from server habc_link
into habc_extraction_schema;

no tables showed in the habc_extraction_schema.  "habc" schema in our postgesql database has many 
tables, but there was no upper case "HABC" schema; so no tables/views were found.  My work around 
for this was to create upper case schema and upper case views (with upper case column names) in 
Postgres.  Is there a simpler way to do this?


2.  The second, larger problem I had, is that the Lucid server crashes when I try to load in large 
amounts of data from our postgresql database.  The script I used to load data and the resulting 
error message are listed below.  The master_grid table I am trying to load from contains approx. 
270,000,000 rows (and about 50 columns; approx 30G of data).  If I make a subset of the table that 
is approximately 1 million rows (and 5 columns) I can load that data fine.  Any ideas on how to 
resolve this issue?

We are running LucidDB (version 0.6.0) on linux [Centos v4.4, Kernal v2.6.9].  Java Version: 1.6.0_01

Thanks!
Emily

SAMPLE LOADING SCRIPT:

--create server link
create server habc_link
foreign data wrapper sys_jdbc
options(
   driver_class 'org.postgresql.Driver',
   url 'jdbc:postgresql://dbserver:port/dbname',
   user_name 'user'
);

--create transformation schema
create schema habc_transformation_schema;

--import the postgresql habc schema
import foreign schema habc
from server habc_link
into habc_extraction_schema;

--the postgresql habc schema has a master_grid table
create view habc_transformation_schema.location_view as
select x,y
from habc_extraction_schema.master_grid;

create schema habc;

create table habc.location_dimension(
     loc_key int generated always as identity not null primary key,
     x integer not null,
     y integer not null,
     unique(x,y)
);

--This is where the data is loaded and cases the server to crash;
insert into habc.location_dimension (x,y)
select x,y from habc_transformation_schema.location_view;

ERROR MESSAGE:

#
# An unexpected error has been detected by Java Runtime Environment:
#
#  SIGSEGV (0xb) at pc=0xb4dba792, pid=31847, tid=2727599024
#
# Java VM: Java HotSpot(TM) Client VM (1.6.0_01-b06 mixed mode, sharing)
# Problematic frame:
# C  [libfennel_btree.so+0x1d792]  _ZN6fennel11BTreeReader9endSearchEv+0x12
#
# An error report file with more information is saved as hs_err_pid31847.log
#
# If you would like to submit a bug report, please visit:
#   http://java.sun.com/webapps/bugreport/crash.jsp
#
*** CAUGHT SIGNAL 6; BACKTRACE:
/mnt/lucid/luciddb-0.6.0/lib/fennel/libfennel_common.so(fennel::AutoBacktrace::signal_handler(int)+0x37) 
[0x179f7]
/lib/tls/libpthread.so.0 [0xa01898]
/lib/ld-linux.so.2 [0x7227a2]
/lib/tls/libc.so.6(gsignal+0x55) [0x7677a5]
/lib/tls/libc.so.6(abort+0xe9) [0x769209]
/usr/java/jre1.6.0_01/lib/i386/client/libjvm.so [0x630358b]
/usr/java/jre1.6.0_01/lib/i386/client/libjvm.so [0x63ae3c1]
/usr/java/jre1.6.0_01/lib/i386/client/libjvm.so(JVM_handle_linux_signal+0x1f0) [0x63079c0]
/usr/java/jre1.6.0_01/lib/i386/client/libjvm.so [0x6305278]
/lib/tls/libpthread.so.0 [0xa01890]
/mnt/lucid/luciddb-0.6.0/lib/fennel/libfennel_lu_colstore.so(fennel::LbmSplicerExecStream::closeImpl()+0x28) 
[0x62718]
/mnt/lucid/luciddb-0.6.0/lib/fennel/libfennel_common.so(fennel::ClosableObject::close()+0x1e) [0x1d29e]
/mnt/lucid/luciddb-0.6.0/lib/fennel/libfennel_exec.so(fennel::ExecStreamGraphImpl::closeImpl()+0x26b) 
[0x51d4b]
/mnt/lucid/luciddb-0.6.0/lib/fennel/libfennel_common.so(fennel::ClosableObject::close()+0x1e) [0x1d29e]
/mnt/lucid/luciddb-0.6.0/lib/fennel/libfarrago.so(Java_net_sf_farrago_fennel_FennelStorage_tupleStreamGraphClose+0x170) 
[0xb4f45f30]
[0xb5d0267e]
[0xb5cfae9d]
[0xb5cfae9d]
[0xb5cfae9d]
[0xb5cfae9d]
[0xb5cfb379]
[0xb5cfb213]
[0xb5cfb379]
[0xb5cfb14d]
[0xb5cfb213]
[0xb5cfad37]
[0xb5cfad37]
[0xb5cfb213]
[0xb5fccc81]
[0xb5fce0d3]
[0xb5cfad37]
[0xb5cfb379]
./lucidDbServer: line 9: 31847 Aborted                 ${JAVA_EXEC} ${JAVA_ARGS} 
com.lucidera.farrago.LucidDbServer



Paul Ramsey wrote:
> 
> Hi folks,
> 
> We are going a project for which Lucid and OLAP tools look like an 
> excellent choice.  It goes something like this:
> 
> - Divide the province of British Columbia up into 100M equally sized 
> squares.
> - For each square, measure a few hundred different environmental and 
> topographic variables.
> - Allow people to summarize information about the province by 
> arbitrarily grouping up the squares.
> 
> In OLAP terms it means we will have a system with between 100M and 200M 
> facts, 50-100 or so dimensions and 50-100 or so measurements.
> 
> As you can imagine, working with transactional databases is starting to 
> get unwieldy.  We found Lucid and tried to give it a go, but have been 
> stymied at the data loading stage. I'll leave it to my colleague to 
> describe our particular environment and techniques.
> 
> Paul
>