718183_en-US

liliconcom

How to load a very large database

I'm trying to load a very large database using JMP but I'm running out of memory. It contains almost 1k columns and millions of rows. The file size is almost 35 GB. Is there any way that I can open only selected columns to minimize memory usage? Any recommendations on how to open such a large file using JMP? Unfortunately, I don't have coding experience

Data AccessmacOSRe: How to load a very large database

I'm surprised nobody pointed this out, but you certainly can use Query Builder for a .csv file.  Just choose the correct driver for a .csv file and you can decide which columns/rows to filter from your large database.

Re: How to load a very large database

Ah, it's a .csv file.  Use "Open" and choose the "Data (Using Preview)" option.

 

Then choose your subset preferences.

 

 

Re: How to load a very large database

You can edit the script that imports the file to omit columns and subset the data.

Re: How to load a very large database

I appreciate your effort in putting together this comprehensive explanation. The first option does not work unless the file is a JMP file. I've tried all other options and it's not executing the second part of the code. It only opens the file without selecting or ignoring the specific columns I want. I've tried the example you provided and it worked but when I choose the file I want, the select or ignore function does not work.

Re: How to load a very large databaseI have the data as .csv file. I couldn't open it using query builderRe: How to load a very large databaseThanks, I have the data as .csv file. Is there a different way to open it? Or I need to upload the file to a specific server and then do query builder?Re: How to load a very large database

You could also try Select Columns when you open the file

Script created by it

Open("$SAMPLE_DATA/Big Class.jmp", Select Columns("name", "age", "height"));

This will let you limit the column selection, but I'm not sure if it will work for you.

 

Open also has other options to limit the data, you can open first x rows, last x rows or random x rows

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/CrimeData.jmp", First(10));
dt = Open("$SAMPLE_DATA/CrimeData.jmp", Random(10));
dt = Open("$SAMPLE_DATA/CrimeData.jmp", Last(10));

and instead of selecting specific columns, you can ignore specific columns

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp", ignore columns("age"));

 

One of the Open items in scripting index has some of these examples

 

Re: How to load a very large database

To add to Jim's post, Query Builder allows you to sample x% of the rows or choose the first (or random) x rows, as well.

Re: How to load a very large database

Using

    File=>Database=>Query Builder

you should be able to select only the columns you want, from the tables you want

 

I suspect, other Community Members may have additional suggestions.

Re: How to load a very large database

Thanks! It worked for me

Re: How to load a very large database

Thanks