Return to Question

I have previously posted on stackoverflow, however, I have been directed here.

I'm getting to the stage where I need to analyse lots of flat CSVs:

• Read-in around 500GB of CSVs (e.g. daily data split by month)
• Group the data (e.g. by month or year)
• Output the aggregated data as a small CSV

I wasn't sure whether sqlite was the right package for this (adding CSVs to it appears to take a long time and as in my link above after a certain size I can't access the database) and whether I should consider something else like PostgreSQL or Microsoft SQL Server?

I'm looking to invest in a hardware/software platform for this (e.g. SSD, RAID, Microsoft SQL Server) and was hoping for some information on where to begin.

Particularly, if PostgreSQL is a possibility - is there a similar way to quickly import CSVs like here:

https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/

Edit (08/10/15):

I'm testing out uploading the data into a PostgreSQL database and am averaging 16 minutes per 10GB CSV. My issue is that some of my columns are very big so I would have to change them from bigint to varchar, etc. The data has 38 columns and around 50 mill rows per file so figuring out which categorical variables are strings or integers is a real pain.

At the moment I am using: cur.copy_expert(sql="COPY %s FROM stdin DELIMITERS '~' CSV;", file=f), with my data-type mainly being varchar. I did this because the CSV is a bit messy and sometimes what I think is an integer turns out be alphanumeric and I have to re-upload.

Would it be much slower to import as a Pandas data-frame (so that panda takes care of column-type for me) and use that with sqlachemy to insert into Postgres? I'm guessing if PANDAs is a bit slower it will make up for it because the column types will be optimised (since it will decide on that for me).

E.g. something like this:

import pandas as pd
df = pd.read_csv('mypath.csv')

from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/dbname')

df.to_sql("table", engine)

My main goal is to optimise this for the group-by command.

Second Edit:

Ahh, I guess best thing is to upload one CSV file using PANDAS and then copy the table structure into a new table which is then filled with the faster COPY command?

I have previously posted on stackoverflow, however, I have been directed here.

I'm getting to the stage where I need to analyse lots of flat CSVs:

• Read-in around 500GB of CSVs (e.g. daily data split by month)
• Group the data (e.g. by month or year)
• Output the aggregated data as a small CSV

I wasn't sure whether sqlite was the right package for this (adding CSVs to it appears to take a long time and as in my link above after a certain size I can't access the database) and whether I should consider something else like PostgreSQL or Microsoft SQL Server?

I'm looking to invest in a hardware/software platform for this (e.g. SSD, RAID, Microsoft SQL Server) and was hoping for some information on where to begin.

Particularly, if PostgreSQL is a possibility - is there a similar way to quickly import CSVs like here:

https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/

Edit (08/10/15):

I'm testing out uploading the data into a PostgreSQL database and am averaging 16 minutes per 10GB CSV. My issue is that some of my columns are very big so I would have to change them from bigint to varchar, etc. The data has 38 columns and around 50 mill rows per file so figuring out which categorical variables are strings or integers is a real pain.

At the moment I am using: cur.copy_expert(sql="COPY %s FROM stdin DELIMITERS '~' CSV;", file=f), with my data-type mainly being varchar. I did this because the CSV is a bit messy and sometimes what I think is an integer turns out be alphanumeric and I have to re-upload.

Would it be much slower to import as a Pandas data-frame (so that panda takes care of column-type for me) and use that with sqlachemy to insert into Postgres? I'm guessing if PANDAs is a bit slower it will make up for it because the column types will be optimised (since it will decide on that for me).

E.g. something like this:

import pandas as pd
df = pd.read_csv('mypath.csv')

from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/dbname')

df.to_sql("table", engine)

My main goal is to optimise this for the group-by command.

Second Edit:

Ahh, I guess best thing is to upload one CSV file using PANDAS and then copy the table structure into a new table which is then filled with the faster COPY command?

I have previously posted on stackoverflow, however, I have been directed here.

I'm getting to the stage where I need to analyse lots of flat CSVs:

• Read-in around 500GB of CSVs (e.g. daily data split by month)
• Group the data (e.g. by month or year)
• Output the aggregated data as a small CSV

I wasn't sure whether sqlite was the right package for this (adding CSVs to it appears to take a long time and as in my link above after a certain size I can't access the database) and whether I should consider something else like PostgreSQL or Microsoft SQL Server?

I'm looking to invest in a hardware/software platform for this (e.g. SSD, RAID, Microsoft SQL Server) and was hoping for some information on where to begin.

Particularly, if PostgreSQL is a possibility - is there a similar way to quickly import CSVs like here:

https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/

Edit (08/10/15):

I'm testing out uploading the data into a PostgreSQL database and am averaging 16 minutes per 10GB CSV. My issue is that some of my columns are very big so I would have to change them from bigint to varchar, etc. The data has 38 columns and around 50 mill rows per file so figuring out which categorical variables are strings or integers is a real pain.

At the moment I am using: cur.copy_expert(sql="COPY %s FROM stdin DELIMITERS '~' CSV;", file=f), with my data-type mainly being varchar. I did this because the CSV is a bit messy and sometimes what I think is an integer turns out be alphanumeric and I have to re-upload.

Would it be much slower to import as a Pandas data-frame (so that panda takes care of column-type for me) and use that with sqlachemy to insert into Postgres? I'm guessing if PANDAs is a bit slower it will make up for it because the column types will be optimised (since it will decide on that for me).

E.g. something like this:

import pandas as pd
df = pd.read_csv('mypath.csv')

from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/dbname')

df.to_sql("table", engine)

My main goal is to optimise this for the group-by command.

I have previously posted on stackoverflow, however, I have been directed here.

I'm getting to the stage where I need to analyse lots of flat CSVs:

• Read-in around 500GB of CSVs (e.g. daily data split by month)
• Group the data (e.g. by month or year)
• Output the aggregated data as a small CSV

I wasn't sure whether sqlite was the right package for this (adding CSVs to it appears to take a long time and as in my link above after a certain size I can't access the database) and whether I should consider something else like PostgreSQL or Microsoft SQL Server?

I'm looking to invest in a hardware/software platform for this (e.g. SSD, RAID, Microsoft SQL Server) and was hoping for some information on where to begin.

Particularly, if PostgreSQL is a possibility - is there a similar way to quickly import CSVs like here:

https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/

Edit (08/10/15):

I'm testing out uploading the data into a PostgreSQL database and am averaging 16 minutes per 10GB CSV. My issue is that some of my columns are very big so I would have to change them from bigint to varchar, etc. The data has 38 columns and around 50 mill rows per file so figuring out which categorical variables are strings or integers is a real pain.

At the moment I am using: cur.copy_expert(sql="COPY %s FROM stdin DELIMITERS '~' CSV;", file=f), with my data-type mainly being varchar. I did this because the CSV is a bit messy and sometimes what I think is an integer turns out be alphanumeric and I have to re-upload.

Would it be much slower to import as a Pandas data-frame (so that panda takes care of column-type for me) and use that with sqlachemy to insert into Postgres? I'm guessing if PANDAs is a bit slower it will make up for it because the column types will be optimised (since it will decide on that for me).

E.g. something like this:

import pandas as pd
df = pd.read_csv('mypath.csv')

from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/dbname')

df.to_sql("table", engine)

My main goal is to optimise this for the group-by command.

Second Edit:

Ahh, I guess best thing is to upload one CSV file using PANDAS and then copy the table structure into a new table which is then filled with the faster COPY command?

I have previously posted on stackoverflow, however, I have been directed here.

I'm getting to the stage where I need to analyse lots of flat CSVs:

• Read-in around 500GB of CSVs (e.g. daily data split by month)
• Group the data (e.g. by month or year)
• Output the aggregated data as a small CSV

I wasn't sure whether sqlite was the right package for this (adding CSVs to it appears to take a long time and as in my link above after a certain size I can't access the database) and whether I should consider something else like PostgreSQL or Microsoft SQL Server?

I'm looking to invest in a hardware/software platform for this (e.g. SSD, RAID, Microsoft SQL Server) and was hoping for some information on where to begin.

Particularly, if PostgreSQL is a possibility - is there a similar way to quickly import CSVs like here:

https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/

Edit (08/10/15):

I'm testing out uploading the data into a PostgreSQL database and am averaging 16 minutes per 10GB CSV. My issue is that some of my columns are very big so I would have to change them from bigint to varchar, etc. The data has 38 columns and around 50 mill rows per file so figuring out which categorical variables are strings or integers is a real pain.

At the moment I am using: cur.copy_expert(sql="COPY %s FROM stdin DELIMITERS '~' CSV;", file=f), with my data-type mainly being varchar. I did this because the CSV is a bit messy and sometimes what I think is an integer turns out be alphanumeric and I have to re-upload.

Would it be much slower to import as a Pandas data-frame (so that panda takes care of column-type for me) and use that with sqlachemy to insert into Postgres? I'm guessing if PANDAs is a bit slower it will make up for it because the column types will be optimised (since it will decide on that for me).

E.g. something like this:

import pandas as pd
df = pd.read_csv('mypath.csv')

from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/dbname')

df.to_sql("table", engine)

My main goal is to optimise this for the group-by command.

I have previously posted on stackoverflow, however, I have been directed here.

I'm getting to the stage where I need to analyse lots of flat CSVs:

• Read-in around 500GB of CSVs (e.g. daily data split by month)
• Group the data (e.g. by month or year)
• Output the aggregated data as a small CSV

I wasn't sure whether sqlite was the right package for this (adding CSVs to it appears to take a long time and as in my link above after a certain size I can't access the database) and whether I should consider something else like PostgreSQL or Microsoft SQL Server?

I'm looking to invest in a hardware/software platform for this (e.g. SSD, RAID, Microsoft SQL Server) and was hoping for some information on where to begin.

Particularly, if PostgreSQL is a possibility - is there a similar way to quickly import CSVs like here:

https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/

Edit (08/10/15):

I'm testing out uploading the data into a PostgreSQL database and am averaging 16 minutes per 10GB CSV. My issue is that some of my columns are very big so I would have to change them from bigint to varchar, etc. The data has 38 columns and around 50 mill rows per file so figuring out which categorical variables are strings or integers is a real pain.

At the moment I am using: cur.copy_expert(sql="COPY %s FROM stdin DELIMITERS '~' CSV;", file=f), with my data-type mainly being varchar. I did this because the CSV is a bit messy and sometimes what I think is an integer turns out be alphanumeric and I have to re-upload.

Would it be much slower to import as a Pandas data-frame (so that panda takes care of column-type for me) and use that with sqlachemy to insert into Postgres? I'm guessing if PANDAs is a bit slower it will make up for it because the column types will be optimised (since it will decide on that for me).

E.g. something like this:

import pandas as pd
df = pd.read_csv('mypath.csv')

from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/dbname')

df.to_sql("table", engine)

My main goal is to optimise this for the group-by command.