Yaas. Very excited to see this.
On Wednesday, October 7, 2015 at 6:07:44 AM UTC-7, Jacob Quinn wrote: > > Haha, nice timing. I just pushed a big CSV.jl overhaul for 0.4 yesterday > afternoon. I just pushed the DataStreams.jl package, so you can find that > at https://github.com/quinnj/DataStreams.jl, and you'll have to Pkg.clone > it. Everything should work at that point. > > I'm still cleaning up some other related packages, so that's why things > aren't documented/registered/tagged quite yet as the interface may evolve > slightly, probably more the low-level machinery. So `stream!(::CSV.Source, > ::DataStream)` should stay the same. > > I've already got a bit writeup started once everything's done, so if you'd > rather wait another couple days or a week, I should have something ready by > then. > > -Jacob > > On Wed, Oct 7, 2015 at 12:33 AM, bernhard <[email protected] <javascript:> > > wrote: > >> Is there any update on this? Or maybe a timeline/roadmap? >> I would love to see a faster CSV reader. >> >> I tried to take a look at Jacob's CSV.jl. >> But I seem to be missing https://github.com/lindahua/DataStreams.jl >> I have no idea where to find DataStreams package.... >> Does it still exist? >> >> Is there any (experimental) way to make CSV.jl work? >> >> >> >>> Am Samstag, 6. Juni 2015 14:41:36 UTC+2 schrieb David Gold: >>> >>> @Jacob, >>> >>> Thank you very much for your explanation! I expect having such a >>> blueprint will make delving into the actual code more tractable for me. >>> I'll be curious to see how your solution here and your proposal for string >>> handling end up playing with the current Julia data ecosystem. >>> >>> On Saturday, June 6, 2015 at 1:17:34 AM UTC-4, Jacob Quinn wrote: >>>> >>>> @David, >>>> >>>> Sorry for the slow response. It's been a busy week :) >>>> >>>> Here's a quick rundown of the approach: >>>> >>>> - In the still-yet-to-be-officially-published >>>> https://github.com/quinnj/CSV.jl package, the bulk of the code goes >>>> into creating a `CSV.File` type where the structure/metadata of the file >>>> is >>>> parsed/detected/saved in a type (e.g. header, delimiter, newline, # of >>>> columns, detected column types, etc.) >>>> - `SQLite.create` and now `CSV.read` both take a `CSV.File` as input >>>> and follow a similar process in parsing: >>>> - The actual file contents are mmapped; i.e. the entire file is >>>> loaded into memory at once >>>> - There are currently three `readfield` methods (Int,Float64,String) >>>> that take an open `CSV.Stream` type (which holds the mmapped data and the >>>> current "position" of parsing), and read a single field according to what >>>> the type of that column is supposed to be >>>> - for example, readfield(io::CSV.Stream, ::Type{Float64}, row, >>>> col), will start reading at the current position of the `CSV.Stream` until >>>> it hits the next delimiter, newline, or end of the file and then interpret >>>> the contents as a Float64, returning `val, isnull` >>>> >>>> That's pretty much it. One of the most critical performance keys for >>>> both SQLite and CSV.read is non-copying strings once the file has been >>>> mmapped. For SQLite, the sqlite3_bind_text library method actually has a >>>> flag to indicate whether the text should be copied or not, so we're able >>>> to >>>> pass the pointer to the position in the mmapped array directly. For the >>>> CSV.read method, which returns a Vector of the columns (as typed arrays), >>>> I've actually rolled a quick and dirty CString type that looks like >>>> >>>> immutable CString >>>> ptr::Ptr{UInt8} >>>> len::Int >>>> end >>>> >>>> With a few extra method definitions, this type looks very close to a >>>> real string type, but we can construct it by pointing directly to the >>>> mmapped region (which currently isn't possible for native Julia string >>>> types). See https://github.com/quinnj/Strings.jl for more >>>> brainstorming around this alternative string implementation. You can >>>> convert a CString to a Julia string by calling string(x::CString) or >>>> map(string,column) for an Array of CSV.CStrings. >>>> >>>> As an update on the performance on the Facebook Kaggle competition >>>> bids.csv file: >>>> >>>> -readcsv: 45 seconds, 33% gc time >>>> -CSV.read: 19 seconds, 3% gc time >>>> -SQLite.create: 25 seconds, 3.25% gc time >>>> >>>> Anyway, hopefully I'll get around to cleaning up CSV.jl to be released >>>> officially, but it's that last 10-20% that's always the hardest to finish >>>> up :) >>>> >>>> -Jacob >>>> >>>> >>>> >>>> On Mon, Jun 1, 2015 at 4:25 PM, David Gold <[email protected]> wrote: >>>> >>>>> @Jacob I'm just developing a working understanding of these issues. >>>>> Would you please help me to get a better handle on your solution? >>>>> >>>>> My understanding thus far: Reading a (local) .csv file into a >>>>> DataFrame using DataFrames.readtable involves reading the file into an >>>>> IOStream and then parsing that stream into a form amenable to parsing by >>>>> DataFrames.builddf, which builds the DataFrame object returned by >>>>> readtable. The work required to get the contents of the .csv file into >>>>> memory in a form that can be manipulated by Julia functions is >>>>> work-intensive in this manner. However, with SQLite, the entire file can >>>>> just be thrown into memory wholesale, along with some metadata (maybe not >>>>> the right term?) that delineates the tabular properties of the data. >>>>> >>>>> What I am curious about, then (if this understanding is not too >>>>> misguided), is how SQLite returns, say, a column of data that doesn't >>>>> include, say, a bunch of delimiters. That is, what sort of parsing *does* >>>>> SQLite do, and when? >>>>> >>>>> On Monday, June 1, 2015 at 1:48:16 PM UTC-4, Jacob Quinn wrote: >>>>>> >>>>>> The biggest single advantage SQLite has is the ability to mmap a file >>>>>> and just tell SQLite which pointer addresses start strings and how long >>>>>> they are, all without copying. The huge, huge bottleneck in most >>>>>> implementations, is not just identifying where a string starts and how >>>>>> long >>>>>> it is, but then allocating "in program" memory and copying the string >>>>>> into >>>>>> it. With SQLite, we can use an in-memory database, mmap the file, and >>>>>> tell >>>>>> SQLite where each string for a column lives by giving it the starting >>>>>> pointer address and how long it is. I've been looking into how to solve >>>>>> this problem over the last month or so (apart from Oscar's gc wizardry) >>>>>> and >>>>>> it just occurred to me last week that using SQLite may be the best way; >>>>>> so >>>>>> far, the results are promising! >>>>>> >>>>>> -Jacob >>>>>> >>>>>> On Mon, Jun 1, 2015 at 11:40 AM, <[email protected]> wrote: >>>>>> >>>>>>> Great, thank you Jacob, I will try it out! >>>>>>> >>>>>>> Do you have a writeup on differences in the way you read CSV files >>>>>>> and the way it is currently done in Julia? Would love to know more! >>>>>>> >>>>>>> Obvious perhaps but for completeness: Reading the data using readcsv >>>>>>> or readdlm does not improve much the metrics I reported, suggesting >>>>>>> that >>>>>>> the overhead from DataFrames is not much. >>>>>>> >>>>>>> Thank you again! >>>>>>> >>>>>>> On Monday, June 1, 2015 at 1:06:50 PM UTC-4, Jacob Quinn wrote: >>>>>>>> >>>>>>>> I've been meaning to clean some things up and properly release the >>>>>>>> functionality, but I have a new way to read in CSV files that beats >>>>>>>> anything else out there that I know of. To get the functionality, >>>>>>>> you'll >>>>>>>> need to be running 0.4 master, then do >>>>>>>> >>>>>>>> Pkg.add("SQLite") >>>>>>>> Pkg.checkout("SQLite","jq/updates") >>>>>>>> Pkg.clone("https://github.com/quinnj/CSV.jl";) >>>>>>>> Pkg.clone("https://github.com/quinnj/Mmap.jl";) >>>>>>>> >>>>>>>> I then ran the following on the bids.csv file >>>>>>>> >>>>>>>> using SQLite, CSV >>>>>>>> >>>>>>>> db = SQLite.SQLiteDB() >>>>>>>> >>>>>>>> ff = CSV.File("/Users/jacobquinn/Downloads/bids.csv") >>>>>>>> >>>>>>>> @time lines = SQLite.create(db, ff,"temp2") >>>>>>>> >>>>>>>> It took 18 seconds on my newish MBP. From the R data.table package, >>>>>>>> the `fread` is the other fastest CSV I know of and it took 34 seconds >>>>>>>> on my >>>>>>>> machine. I'm actually pretty surprised by that, since in other tests >>>>>>>> I've >>>>>>>> done it was on par with the SQLite+CSV or sometimes slightly faster. >>>>>>>> >>>>>>>> Now, you're not necessarily getting a Julia structure in this case, >>>>>>>> but it's loading the data into an SQLite table, that you can then run >>>>>>>> SQLite.query(db, sql_string) to do manipulations and such. >>>>>>>> >>>>>>>> -Jacob >>>>>>>> >>>>>>>> >>>>>>>> On Sun, May 31, 2015 at 9:42 PM, <[email protected]> wrote: >>>>>>>> >>>>>>>>> Thank you Tim and Jiahao for your responses. Sorry, I did not >>>>>>>>> mention in my OP that I was using Version 0.3.10-pre+1 (2015-05-30 >>>>>>>>> 11:26 >>>>>>>>> UTC) Commit 80dd75c* (1 day old release-0.3). >>>>>>>>> >>>>>>>>> I tried other releases as Tim suggested: >>>>>>>>> >>>>>>>>> On Version 0.4.0-dev+5121 (2015-05-31 12:13 UTC) Commit bfa8648* >>>>>>>>> (0 days old master), >>>>>>>>> the same command takes 14 minutes - half that it was taking with >>>>>>>>> release-0.3 but still 3 times more than that taken by R's read.csv (5 >>>>>>>>> min). >>>>>>>>> More important, Julia process takes up 8GB memory (Rsession takes >>>>>>>>> 1.6GB) >>>>>>>>> output of the command `@time DataFrames.readtable("bids.csv");` is >>>>>>>>> 857.120 seconds (352 M allocations: 16601 MB, 71.59% gc time) >>>>>>>>> # reduced from 85% to 71% >>>>>>>>> >>>>>>>>> For completeness, On Version 0.4.0-dev+4451 (2015-04-22 21:55 UTC) >>>>>>>>> ob/gctune/238ed08* (fork: 1 commits, 39 days), the command `@time >>>>>>>>> DataFrames.readtable("bids.csv");` takes 21 minutes; the output of >>>>>>>>> the >>>>>>>>> macro is: >>>>>>>>> elapsed time: 1303.167204109 seconds (18703 MB allocated, 76.58% >>>>>>>>> gc time in 33 pauses with 31 full sweep) >>>>>>>>> The process also takes up 8GB memory on the machine, more than the >>>>>>>>> earlier one. My machine has also significantly slowed down - so >>>>>>>>> perhaps the >>>>>>>>> increase in memory when compared to release-0.3 is significant. >>>>>>>>> >>>>>>>>> On disabling gc, my machine (4GB laptop) goes soul searching; so >>>>>>>>> its not an option for now. >>>>>>>>> >>>>>>>>> Is this the best one can expect for now? I read the discussion on >>>>>>>>> issue #10428 but I did not understand it well :-( >>>>>>>>> >>>>>>>>> Thank you! >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> On Sunday, May 31, 2015 at 9:25:14 PM UTC-4, Jiahao Chen wrote: >>>>>>>>>> >>>>>>>>>> Not ideal, but for now you can try turning off the garbage >>>>>>>>>> collection while reading in the DataFrame. >>>>>>>>>> >>>>>>>>>> gc_disable() >>>>>>>>>> df = DataFrames.readtable("bids.csv") >>>>>>>>>> gc_enable() >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Thanks, >>>>>>>>>> >>>>>>>>>> Jiahao Chen >>>>>>>>>> Research Scientist >>>>>>>>>> MIT CSAIL >>>>>>>>>> >>>>>>>>>> On Mon, Jun 1, 2015 at 1:36 AM, Tim Holy <[email protected]> >>>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>>> If you're using julia 0.3, you might want to try current master >>>>>>>>>>> and/or >>>>>>>>>>> possibly the "ob/gctune" branch. >>>>>>>>>>> >>>>>>>>>>> https://github.com/JuliaLang/julia/issues/10428 >>>>>>>>>>> >>>>>>>>>>> Best, >>>>>>>>>>> --Tim >>>>>>>>>>> >>>>>>>>>>> On Sunday, May 31, 2015 09:50:03 AM [email protected] wrote: >>>>>>>>>>> > Facebook's Kaggle competition has a dataset with ~7.6e6 rows >>>>>>>>>>> with 9 columns >>>>>>>>>>> > (mostly >>>>>>>>>>> > strings). >>>>>>>>>>> https://www.kaggle.com/c/facebook-recruiting-iv-human-or-bot/data >>>>>>>>>>> > >>>>>>>>>>> > Loading the dataset in R using read.csv takes 5 minutes and >>>>>>>>>>> the resulting >>>>>>>>>>> > dataframe takes 0.6GB (RStudio takes a total of 1.6GB memory >>>>>>>>>>> on my machine) >>>>>>>>>>> > >>>>>>>>>>> > >t0 = proc.time(); a = read.csv("bids.csv"); proc.time()-t0 >>>>>>>>>>> > >>>>>>>>>>> > user system elapsed >>>>>>>>>>> > 332.295 4.154 343.332 >>>>>>>>>>> > >>>>>>>>>>> > > object.size(a) >>>>>>>>>>> > >>>>>>>>>>> > 601496056 bytes #(0.6 GB) >>>>>>>>>>> > >>>>>>>>>>> > Loading the same dataset using DataFrames' readtable takes >>>>>>>>>>> about 30 minutes >>>>>>>>>>> > on the same machine (varies a bit, lowest is 25 minutes) and >>>>>>>>>>> the resulting >>>>>>>>>>> > (Julia process, REPL on Terminal, takes 6GB memory on the same >>>>>>>>>>> machine) >>>>>>>>>>> > >>>>>>>>>>> > (I added couple of calls to @time macro inside the readtable >>>>>>>>>>> function to >>>>>>>>>>> > see whats taking time - outcomes of these calls too are below) >>>>>>>>>>> > >>>>>>>>>>> > julia> @time DataFrames.readtable("bids.csv"); >>>>>>>>>>> > WARNING: Begin readnrows call >>>>>>>>>>> > elapsed time: 29.517358476 seconds (2315258744 bytes >>>>>>>>>>> allocated, 0.35% gc >>>>>>>>>>> > time) >>>>>>>>>>> > WARNING: End readnrows call >>>>>>>>>>> > WARNING: Begin builddf call >>>>>>>>>>> > elapsed time: 1809.506275842 seconds (18509704816 bytes >>>>>>>>>>> allocated, 85.54% >>>>>>>>>>> > gc time) >>>>>>>>>>> > WARNING: End builddf call >>>>>>>>>>> > elapsed time: 1840.471467982 seconds (21808681500 bytes >>>>>>>>>>> allocated, 84.12% >>>>>>>>>>> > gc time) #total time for loading >>>>>>>>>>> > >>>>>>>>>>> > >>>>>>>>>>> > Can you please suggest how I can improve load time and memory >>>>>>>>>>> usage in >>>>>>>>>>> > DataFrames for sizes this big and bigger? >>>>>>>>>>> > >>>>>>>>>>> > Thank you! >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>> >>>>>> >>>> >
