topic Re: How to load a very large database in Discussions

How to load a very large database

AliSheffeh — Tue, 23 Jan 2024 17:01:57 GMT

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

Re: How to load a very large database

txnelson — Tue, 23 Jan 2024 17:43:11 GMT

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

mmarchandTSI — Tue, 23 Jan 2024 17:50:12 GMT

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

jthi — Tue, 23 Jan 2024 18:12:58 GMT

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

AliSheffeh — Tue, 23 Jan 2024 18:13:52 GMT

Thanks, 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

AliSheffeh — Tue, 23 Jan 2024 18:14:36 GMT

I have the data as .csv file. I couldn't open it using query builder

Re: How to load a very large database

AliSheffeh — Tue, 23 Jan 2024 19:23:18 GMT

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 database

Craige_Hales — Tue, 23 Jan 2024 20:39:35 GMT

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

(view in My Videos)

Re: How to load a very large database

mmarchandTSI — Tue, 23 Jan 2024 21:12:32 GMT

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

dlehman1 — Sun, 28 Jan 2024 18:17:36 GMT

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

AliSheffeh — Wed, 31 Jan 2024 02:40:12 GMT

Thanks! It worked for me

Re: How to load a very large database

AliSheffeh — Wed, 31 Jan 2024 02:40:39 GMT

Thanks