1 / 39

Enabling Collaborative Research Data Management with SQLShare

This article explores the need for collaborative research data management and introduces SQLShare, a database-as-a-service platform that simplifies data organization, manipulation, and sharing for researchers. It discusses the challenges of spreadsheet-based data management and the advantages of using a cloud-based database system. The article also highlights the features and benefits of SQLShare, including easy data uploading, SQL query capabilities, and collaboration features.

timothyb
Download Presentation

Enabling Collaborative Research Data Management with SQLShare

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Enabling Collaborative Research Data Management with SQLShare Bill Howe, PhD Director of Research, Scalable Data Analytics University of Washington eScience Institute Tom Lewis Director, Academic & Collaborative Applications University of Washington Information Technology Bill Howe, UW

  2. Assessing UW Researchers’ Data Management Needs • Conversations with Research Leaders (2008) • First large-scale assessment of researchers’ needs • 124 Interviews with top researchers • Faculty Technology Survey (2011) • Use of teaching and research technologies • Paired with student and TA surveys • Reached all disciplines, levels of research • 689 instructors responded

  3. Data Management Needs • Expertise—designing new systems, enhancing current practices, analysis • Storage—Large amounts of data for current projects and data archives • Backup—inconsistent systems among researchers, some unreliable practices • Security—secure access needed for inter-institutional partners

  4. Types of Data Stored

  5. Data Storage Location

  6. http://escience.washington.edu

  7. The University of Washington eScience Institute • Rationale • The exponential increase in sensors is transitioning all fields of science and engineering from data-poor to data-rich • Techniques and technologies include • Sensors and sensor networks, databases, data mining, machine learning, visualization, cluster/cloud computing • If these techniques and technologies are not widely available and widely practiced, UW will cease to be competitive • Mission • Help position the University of Washington at the forefront of research both in modern eScience techniques and technologies, and in the fields that depend upon them • Strategy • Bootstrap a cadre of Research Scientists • Add faculty in key fields • Build out a “consultancy” of students and non-research staff Bill Howe, eScience Institute

  8. eScience Big Data Group Bill Howe, Phd (databases, cloud, data-intensive scalable computing, visualization) Staff • Seung-Hee Bae, Phd (postdoc, scalable machine learning algorithms) • Dan Halperin, Phd (postdoc; scalable systems) • Sagar Chitnis, Research Engineer (Azure, databases, web services) • (alumna) Marianne Shaw, Phd (hadoop, semantic graph databases) • (alumna) Alicia Key, Research Engineer (visualization, web applications) Students • Scott Moe (2ndyrPhd, Applied Math) • Daniel Perry (2nd yr Phd, HCDE) Partners • CSE DB Faculty: Magda Balazinska, Dan Suciu • CSE students: Paris Koutris, PrasangUpadhyaya, • UW-IT (web applications, QA/support) • Cecilia Aragon, Phd, Associate Professor, HCDE (visualization, scientific applications) Bill Howe, UW

  9. [src: Carol Goble] • Power distribution • 80:20 rule Head Popularity / Sales Tail Products / Results First published May 2007, Wired Magazine article 2004

  10. [src: Carol Goble] Long Tail of Research Data High throughput experimental methods Industrial scale Commons based production Publicly data sets Cherry picked results Preserved GenBank PDB ChemSpider UniProt CATH, SCOP (Protein Structure Classification) Pfam Spreadsheets, Notebooks Local, Lost

  11. Problem How much time do you spend “handling data”as opposed to “doing science”? “90%” Mode answer: Bill Howe, UW

  12. ANNOTATIONSUMMARY-COMBINEDORFANNOTATION16_Phaeo_genome Simple Example COGAnnotation_coastal_sample.txt SELECT*FROMPhaeo_genome p, coastal_sample c WHERE p.COG_hit = c.hit Bill Howe, UW

  13. Data management becoming the bottleneck • Data is captured and manipulated in spreadsheets • This approach made sense five years ago; the data volumes were manageable • But now: • 50k rows in each of 50-100 spreadsheets, per scientist • Significantly more sharing required: “mega-collabs” • Continuously producing new results • The management overhead is beginning to dominate • Difficult to share data publicly (emailing files is a mess) • Lots of work to get an answers to any new question

  14. Why not build a database? • Assumes an pre-defined schema • more on this later • Requires specialized technical skills and a huge amount of up-front effort • Not a perfect fit – it’s hard to design a “permanent” database for a fast-moving research target • Researchers have little interest in operating and maintaining a data system – they just want to organize, manipulate, and share data

  15. Query Database-as-a-Service for the 99% Approach: Strip down databases to bare essentials • Upload -> Query -> Share • Try to eliminate installation, configuration, schema design, data loading, tuning, app-building easy, thanks to the cloud harder Bill Howe, UW

  16. Getting Data In • Upload data through a browser to a cloud database (so no need to manage a local system) • No need to “design a database” before you use your data – just upload and get to work • Write SQL queries, with some automated help and some guided help from experts • Build on your own results • Output of one query can be the input to another • Encourages sharing and reuse • Avoids everyone on the team running the same data processing steps over and over • Provides provenance – “how did you get this result?” Bill Howe, UW

  17. Select from a list of English descriptions

  18. Edit a Query Save the results, share them with others

  19. http://sqlshare.escience.washington.edu Bill Howe, UW

  20. http://sqlshare.escience.washington.edu Bill Howe, UW

  21. http://sqlshare.escience.washington.edu Bill Howe, UW

  22. http://sqlshare.escience.washington.edu Bill Howe, UW

  23. http://sqlshare.escience.washington.edu Bill Howe, UW

  24. Spreadsheet Crawler Excel Addin Python Client R Client “Flagship” SQLShare App (Python) on EC2 VizDeck ASP.NET OAuth2 SQLShare REST API WCF

  25. Deeply nested hierarchies of views Provenance Controlled Sharing Bill Howe, UW

  26. 9 13 11 14 12 15 16 10 5 4 8 7 6 Robin Kodner 2010 Pilot- Outreach and Education-based sampling: Schooner Adventuress Bill Howe, UW

  27. NatureMapping Program Karen Dvornich Wildlife Observations (1902- ) Data collection and submission options: Download/upload spreadsheet Online data entry NatureTracker on handheld/GPS Android ODK (Open Data Kit) Water Quality Monitoring Sites (2003 - ) Bill Howe, UW

  28. Andrew White, UW Chemistry “An undergraduate student and I are working with gigabytes of tabular data derived from analysis of protein surfaces. Previously, we were using huge directory trees and plain text files. Now we can accomplish a 10 minute 100 line script in 1 line of SQL.” -- Andrew D White Bill Howe, UW

  29. Why SQL? Bill Howe, UW

  30. A “Needs Hierarchy” of Science Data Management “As each need is satisfied, the next higher level in the hierarchy dominates conscious functioning.” -- Maslow 43 full semantic integration analytics query sharing storage Bill Howe, UW

  31. NoSchema (not NoSQL) • A schema* is a shared consensus about some universe of discourse • At the frontier of research, this shared consensus does not exist, by definition • Any schema that does emerge will change frequently, by definition • Data found “in the wild” will typically not conform to any schema, by definition • But this doesn’t mean we have to punt on databases and go back to ad hoc scripts and files *ontology/metadata standard/controlled vocabulary/etc. Bill Howe, UW

  32. A “Needs Hierarchy” of Science Data Management Silos full semantic integration analytics query sharing Cloud storage Bill Howe, UW

  33. What’s the point? • Conventional wisdom says “Science data isn’t relational” • This is utter nonsense • Conventional wisdom says “Scientists won’t write SQL” • This is utter nonsense • So why aren’t databases being used more often? • They’re a PITA • We implicate difficulty in • installation, configuration • schema design, data loading • performance tuning • app-building (NoGUI?) We ask instead, “What kind of platform can support ad hoc scientific Q&A with SQL?” Garret Cole, eScience Institute

  34. Biologists are beginning to write very complex queries (rather than relying on staff programmers) Example: Computing the overlaps of two sets of blast results SELECT x.strain, x.chr, x.region as snp_region, x.start_bp as snp_start_bp , x.end_bp as snp_end_bp, w.start_bp as nc_start_bp, w.end_bp as nc_end_bp , w.category as nc_category , CASE WHEN (x.start_bp >= w.start_bp AND x.end_bp <= w.end_bp) THEN x.end_bp - x.start_bp + 1 WHEN (x.start_bp <= w.start_bp AND w.start_bp <= x.end_bp) THEN x.end_bp - w.start_bp + 1 WHEN (x.start_bp <= w.end_bp AND w.end_bp <= x.end_bp) THEN w.end_bp - x.start_bp + 1 END AS len_overlap FROM [koesterj@washington.edu].[hotspots_deserts.tab] x INNER JOIN [koesterj@washington.edu].[table_noncoding_positions.tab] w ON x.chr = w.chr WHERE (x.start_bp >= w.start_bp AND x.end_bp <= w.end_bp) OR (x.start_bp <= w.start_bp AND w.start_bp <= x.end_bp) OR (x.start_bp <= w.end_bp AND w.end_bp <= x.end_bp) ORDER BY x.strain, x.chr ASC, x.start_bp ASC We see thousands of queries written by non-programmers

  35. So why SQL? • Covers 80% of what we need • Ex: Sloan Digital Sky Survey • Ex: Hybrid Hash Join algorithm published in BMC bioinformatics • Empower a new class of data-savvy scientist who isn’t forced to be trained as an IT professional • Algebraic optimization • Ask me about this if you’re interested • Views: Logical and physical data Independence • Reason about the problem independently of the data representation • No re-execution of “workflows” • No file format incompatibilities • No version mismatches • Data and code tightly coupled and (logically) centralize Bill Howe, UW

  36. SQLShare as a CS Research Platform SSDBM 2011 SIGMOD 2011 (demo) • Automatic “Starter” Queries • (Bill Howe, Garret Cole, NodiraKhoussainova, Leilani Battle) • VizDeck: Automatic Mashups and Visualization • (Bill Howe, Alicia Key, Daniel Perry, Cecilia Aragon) • Info Extraction from Spreadsheets • (Mike Cafarella, Dave Maier, Bill Howe, Sagar Chitnis, Abdu Alwani) • Scalable Analytics-as-a-Service • (Dan Suciu, Magda Balazinska, Bill Howe) • Optimizing Iterative Queries for Machine Learning • (Dan Suciu, Magda Balazinska, Bill Howe) SSDBM 2011 CHI 2012 SIGMOD 2012 (demo) VLDB 2010 Datalog2.0 2012 CIDR 2013 Bill Howe, UW

  37. Local or cloud-hosted deployments • Multi-institution sharing • Global users and permissions • Distributed data and distributed query done! Where we’re headed: We are looking for partners! Bill Howe, UW

  38. http://sqlshare.escience.washington.edu billhowe@cs.washington.edu http://escience.washington.edu Bill Howe, UW

More Related