Upgrade to Pro — share decks privately, control downloads, hide ads and more …

To and Fro from Amazon Redshift

To and Fro from Amazon Redshift

Coursera is an online educational startup with over 19 million learners across the globe. At Coursera we use Redshift as our primary data warehouse as it provides a standard SQL interface and has fast and reliable performance. We use our open-source framework Dataduct to move data to and fro from redshift. In this talk we’ll cover the workflow service at Coursera and how it is now being used for other use cases beyond just ETL such as machine learning, predictions and bulk loading into Cassandra.

Sourabh

May 10, 2016
Tweet

More Decks by Sourabh

Other Decks in Programming

Transcript

  1. Sourabh Bajaj, Software Engineer
    18 March 2016
    To and Fro from Amazon Redshift
    Extending our workflow service for use cases beyond ETL

    View Slide

  2. View Slide

  3. O U R M I S S I O N :
    Universal access to
    the world’s best education

    View Slide

  4. 3.6M
    course
    completions
    1,000+
    active
    courses
    140
    partner
    institutions
    19M
    registered
    learners
    By the numbers

    View Slide

  5. About me
    ● Georgia Tech, CS
    ● Analytics at Coursera
    ● @sb2nov
    Machine Learning
    Distributed Systems
    Teaching and Learning
    Computational Finance

    View Slide

  6. ● Dataduct
    ● 3Cs
    ● Beyond ETL
    Agenda

    View Slide

  7. Dataduct
    1. Wrapper on AWS Data Pipeline
    2. Built in house at Coursera
    3. github.com/coursera/dataduct

    View Slide

  8. Dataduct

    View Slide

  9. Dataduct
    #1: Fault Tolerance
    Recover from machine or transient network failures.

    View Slide

  10. Dataduct
    #1: Fault Tolerance
    Recover from machine or transient network failures.
    #2: Scheduling
    Ability to run at different frequencies

    View Slide

  11. Dataduct
    #1: Fault Tolerance
    Recover from machine or transient network failures.
    #2: Scheduling
    Ability to run at different frequencies
    #3: Resource Management
    Manage EC2 / EMR resources required

    View Slide

  12. Dataduct
    #4: Dependency Management
    Manage dependencies on other pipelines and steps

    View Slide

  13. Dataduct
    #4: Dependency Management
    Manage dependencies on other pipelines and steps
    #5: Extensibility
    Easy to run new types of jobs and steps

    View Slide

  14. Dataduct
    #4: Dependency Management
    Manage dependencies on other pipelines and steps
    #5: Extensibility
    Easy to run new types of jobs and steps
    #6: Developer Friendliness
    Easy development and deployment

    View Slide

  15. 3 Cs
    Collecting Curating Capitalizing

    View Slide

  16. Case Study: Instructor Dashboards

    View Slide

  17. Case study:
    Instructor dashboards
    Student demographics

    View Slide

  18. Student demographics
    Identify learner misconceptions
    Case study:
    Instructor dashboards

    View Slide

  19. Student demographics
    Highlight learner dropout points
    Identify learner misconceptions
    Case study:
    Instructor dashboards

    View Slide

  20. Student demographics
    Highlight learner dropout points
    Identify learner misconceptions
    Case study:
    Instructor dashboards

    View Slide

  21. 3 Cs
    Collecting Curating Capitalizing

    View Slide

  22. Collecting
    Build systems that make it easy to collect data.
    Collecting Curating Capitalizing

    View Slide

  23. Collecting
    services
    Collecting Curating Capitalizing
    web
    mobile
    Eventing
    service
    Redshift

    View Slide

  24. Netflix
    Aegisthus
    Collecting
    Collecting Curating Capitalizing
    Dataduct +
    Redshift
    Collecting

    View Slide

  25. Collecting
    Collecting Curating Capitalizing
    def processBranches(branchPipe: TypedPipe[BranchModel], outputPath: String): Unit = {
    branchPipe
    .map { branch =>
    (StringKey(branch.branchId).key,
    StringKey(branch.courseId).key,
    branch.changesDescription.map(_.value).getOrElse(""))
    }
    .write(TypedTsv[COURSE_BRANCHES_OUTPUT_FORMAT](outputPath))}
    Collecting

    View Slide

  26. Collecting
    ● Definition in YAML
    ● Steps
    ● Visualization
    ● Reusable code
    Collecting Curating Capitalizing
    steps:
    - type: extract-from-rds
    sql: | SELECT instructor_id,
    ,course_id
    ,rank
    FROM courses_instructorincourse;
    hostname: host_db_1
    database: master
    - type: load-into-staging-table
    table: staging.instructors_sessions
    - type: reload-prod-table
    source: staging.instructors_sessions
    destination: prod.instructors_sessions

    View Slide

  27. Case study: Instructor dashboards
    Collecting Curating Capitalizing
    Eventing data
    (Student progress)
    Cassandra data
    (Course content)
    Redshift
    Raw tables
    Learner

    View Slide

  28. 3 Cs
    Collecting Curating Capitalizing

    View Slide

  29. Curating
    Data quality
    Collecting Curating Capitalizing
    1. Correctness
    2. Completeness
    3. Interpretability

    View Slide

  30. Curating
    Standardize the business definitions
    Collecting Curating Capitalizing
    BI
    schema
    raw
    schema
    analyses

    View Slide

  31. Curating
    Standardization
    Collecting Curating Capitalizing
    Steps:
    - step_type: pipeline-dependencies
    name: wait_for_dependencies
    dependent_pipelines:
    - raw_events
    - recommendations
    - step_type: create-update-sql
    name: discovery_clicks
    depends_on: wait_for_dependencies
    script: scripts/bi.discovery_clicks.sql
    table_definition: bi.discovery_clicks.sql
    - step_type: create-update-sql
    name: discovery_impressions
    depends_on: wait_for_dependencies
    script: scripts/bi.discovery_impressions.sql
    table_definition:bi.discovery_impressions.sql

    View Slide

  32. Case study: Instructor dashboards
    Collecting Curating Capitalizing
    Redshift
    BI tables
    Cumulative progress
    per student & course
    Dataduct
    Eventing data
    (Student progress)
    Cassandra data
    (Course content)
    Redshift
    Raw tables
    Learner

    View Slide

  33. 3 Cs
    Collecting Curating Capitalizing

    View Slide

  34. Capitalizing
    Building data products within production ecosystem
    Collecting Curating Capitalizing
    Nostos
    service
    Key/Value access
    Redshift
    Nostos Loader

    View Slide

  35. Capitalizing
    Building data products within production ecosystem
    Collecting Curating Capitalizing

    View Slide

  36. Capitalizing
    Nostos
    Collecting Curating Capitalizing
    steps:
    - step_type: nostos-v2
    job_name: example
    index_column: user_id
    fields:
    - generator: sql_iterator
    sql: >
    SELECT user_id
    ,session_id::VARCHAR AS entityId1
    FROM prod.enrollments
    WHERE user_id < 100
    - generator: sql_iterator
    is_set: true
    sql: >
    SELECT user_id
    ,session_id::VARCHAR AS entityId2
    FROM prod.enrollments
    WHERE user_id < 100

    View Slide

  37. Case study: Instructor dashboards
    Collecting Curating Capitalizing
    Materialized progress
    per course (KVS)
    Nostos
    Instructor
    Redshift
    BI tables
    Cumulative progress
    per student & course
    Dataduct
    Eventing data
    (Student progress)
    Cassandra data
    (Course content)
    Redshift
    Raw tables
    Learner

    View Slide

  38. Beyond ETL
    Why should we do this ?
    1. Leveraging current infrastructure
    accelerates the team
    2. New use cases keep coming up that
    have helped evolve dataduct

    View Slide

  39. Machine Learning
    ● Daily model updates
    ● Multistage model training
    ● Hyperparameter tuning
    ● Performance benchmarks

    View Slide

  40. Experimentation
    ● Hourly result calculations
    ● Parameter updates

    View Slide

  41. Takeaways:
    ● Leveraging common infrastructure can really
    accelerate the team
    ● Good Infrastructure would be used in really
    creative ways which you might not anticipate

    View Slide

  42. Thank You

    View Slide

  43. Questions ?

    View Slide

  44. coursera.org/jobs
    building.coursera.org
    @CourseraEng

    View Slide