Generate LookML.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-20T22:51:16.964582Z",
     "start_time": "2017-06-20T22:50:44.340885Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting git+https://github.com/zzzeek/sqlalchemy\n",
      "  Cloning https://github.com/zzzeek/sqlalchemy to /tmp/pip-w_2vu6w1-build\n",
      "Installing collected packages: SQLAlchemy\n",
      "  Running setup.py install for SQLAlchemy ... \u001b[?25ldone\n",
      "\u001b[?25hSuccessfully installed SQLAlchemy-1.2.0b1.dev0\n",
      "Collecting git+https://github.com/bufferapp/rsdf\n",
      "  Cloning https://github.com/bufferapp/rsdf to /tmp/pip-xekajwng-build\n",
      "Collecting smart_open (from rsdf==0.1.0)\n",
      "  Downloading smart_open-1.5.3.tar.gz\n",
      "Requirement already satisfied: pandas in /opt/conda/lib/python3.6/site-packages (from rsdf==0.1.0)\n",
      "Requirement already satisfied: sqlalchemy in /opt/conda/lib/python3.6/site-packages (from rsdf==0.1.0)\n",
      "Requirement already satisfied: numpy in /opt/conda/lib/python3.6/site-packages (from rsdf==0.1.0)\n",
      "Collecting psycopg2 (from rsdf==0.1.0)\n",
      "  Downloading psycopg2-2.7.1-cp36-cp36m-manylinux1_x86_64.whl (2.7MB)\n",
      "\u001b[K    100% |################################| 2.7MB 393kB/s ta 0:00:01\n",
      "\u001b[?25hCollecting boto>=2.32 (from smart_open->rsdf==0.1.0)\n",
      "  Downloading boto-2.47.0-py2.py3-none-any.whl (1.4MB)\n",
      "\u001b[K    100% |################################| 1.4MB 1.0MB/s ta 0:00:01\n",
      "\u001b[?25hCollecting bz2file (from smart_open->rsdf==0.1.0)\n",
      "  Downloading bz2file-0.98.tar.gz\n",
      "Requirement already satisfied: requests in /opt/conda/lib/python3.6/site-packages (from smart_open->rsdf==0.1.0)\n",
      "Requirement already satisfied: python-dateutil>=2 in /opt/conda/lib/python3.6/site-packages (from pandas->rsdf==0.1.0)\n",
      "Requirement already satisfied: pytz>=2011k in /opt/conda/lib/python3.6/site-packages (from pandas->rsdf==0.1.0)\n",
      "Requirement already satisfied: six>=1.5 in /opt/conda/lib/python3.6/site-packages (from python-dateutil>=2->pandas->rsdf==0.1.0)\n",
      "Building wheels for collected packages: smart-open, bz2file\n",
      "  Running setup.py bdist_wheel for smart-open ... \u001b[?25ldone\n",
      "\u001b[?25h  Stored in directory: /home/local/.cache/pip/wheels/b0/81/ad/856aade935fceaab491a800ec4de58edb8642afa4c4ba91a00\n",
      "  Running setup.py bdist_wheel for bz2file ... \u001b[?25ldone\n",
      "\u001b[?25h  Stored in directory: /home/local/.cache/pip/wheels/31/9c/20/996d65ca104cbca940b1b053299b68459391c01c774d073126\n",
      "Successfully built smart-open bz2file\n",
      "Installing collected packages: boto, bz2file, smart-open, psycopg2, rsdf\n",
      "  Running setup.py install for rsdf ... \u001b[?25ldone\n",
      "\u001b[?25hSuccessfully installed boto-2.47.0 bz2file-0.98 psycopg2-2.7.1 rsdf-0.1.0 smart-open-1.5.3\n",
      "Collecting git+https://github.com/michael-erasmus/lookml-gen\n",
      "  Cloning https://github.com/michael-erasmus/lookml-gen to /tmp/pip-8whjdyu6-build\n",
      "Requirement already satisfied: six>=1.10.0 in /opt/conda/lib/python3.6/site-packages (from lookml-gen==0.1.6)\n",
      "Installing collected packages: lookml-gen\n",
      "  Running setup.py install for lookml-gen ... \u001b[?25ldone\n",
      "\u001b[?25hSuccessfully installed lookml-gen-0.1.6\n"
     ]
    }
   ],
   "source": [
    "!pip install git+https://github.com/zzzeek/sqlalchemy\n",
    "!pip install git+https://github.com/bufferapp/rsdf\n",
    "!pip install git+https://github.com/michael-erasmus/lookml-gen #use upstream repo once my PR is merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-20T23:08:01.560042Z",
     "start_time": "2017-06-20T23:08:01.551736Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import Table\n",
    "from sqlalchemy import MetaData\n",
    "import rsdf\n",
    "\n",
    "def get_table_metadata(table_name):\n",
    "    engine = rsdf.get_engine()\n",
    "    metadata = MetaData()\n",
    "    table = Table(table_name, metadata, autoload=True, autoload_with=engine)\n",
    "    \n",
    "    return table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-20T23:08:01.575142Z",
     "start_time": "2017-06-20T23:08:01.561812Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<lookmlgen.base_generator.GeneratorFormatOptions at 0x7ff93eabc208>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from lookmlgen.base_generator import GeneratorFormatOptions\n",
    "GeneratorFormatOptions(omit_time_frames_if_not_set=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-20T23:08:01.701254Z",
     "start_time": "2017-06-20T23:08:01.578324Z"
    }
   },
   "outputs": [],
   "source": [
    "from lookmlgen.view import View\n",
    "from lookmlgen.field import Dimension, DimensionGroup, Measure\n",
    "from lookmlgen.base_generator import GeneratorFormatOptions\n",
    "\n",
    "def map_column_type(sql_type):\n",
    "    if 'BOOLEAN' in str(sql_type):\n",
    "        return 'yesno'\n",
    "    elif 'NUMERIC' in str(sql_type) or 'INTEGER' in str(sql_type) or 'FLOAT' in str(sql_type):\n",
    "        return 'number'\n",
    "    elif 'TIMESTAMP' in str(sql_type):\n",
    "        return 'time'\n",
    "    else:\n",
    "        return 'string'\n",
    "\n",
    "def map_dimension_field(col):\n",
    "    field = None\n",
    "    col_type = map_column_type(col.type)\n",
    "    \n",
    "    if col.name =='id':\n",
    "        field = Dimension('id', type=col_type, primary_key=True)\n",
    "    elif 'TIMESTAMP' in str(col.type):\n",
    "        field = DimensionGroup(col.name)\n",
    "    else:\n",
    "        field = Dimension(col.name, type=col_type)\n",
    "    \n",
    "    field.label = col.comment\n",
    "    return field\n",
    "    \n",
    "def generate_lookml(table, f):\n",
    "    view = View(table.name, sql_table_name=table.name)\n",
    "\n",
    "    for col in table.columns:\n",
    "        view.add_field(map_dimension_field(col))\n",
    "      \n",
    "    format_options = GeneratorFormatOptions(view_fields_alphabetical=False, \n",
    "                           omit_default_field_type=False,\n",
    "                           omit_time_frames_if_not_set=True)\n",
    "    \n",
    "    view.generate_lookml(f,format_options)\n",
    "    \n",
    "    return f             "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-20T23:08:04.541123Z",
     "start_time": "2017-06-20T23:08:01.704092Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# STOP! This file was generated by an automated process.\n",
      "# Any edits you make will be lost the next time it is\n",
      "# re-generated.\n",
      "view: ad_attributions {\n",
      "  sql_table_name: ad_attributions ;;\n",
      "\n",
      "  dimension: visitor_id {\n",
      "    label: \"What is the visitor_id we attribute the Ad to?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.visitor_id ;;\n",
      "  }\n",
      "\n",
      "  dimension_group: created_at {\n",
      "    label: \"When was the ad attribution event created?\"\n",
      "    type: time\n",
      "    datatype: datetime\n",
      "    sql: ${TABLE}.created_at ;;\n",
      "  }\n",
      "\n",
      "  dimension: client_id {\n",
      "    label: \"What is the client_id that generated the event?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.client_id ;;\n",
      "  }\n",
      "\n",
      "  dimension: adgroup_id {\n",
      "    label: \"What is the unique identifier for the adgroup associated with this event?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.adgroup_id ;;\n",
      "  }\n",
      "\n",
      "  dimension: adgroup_name {\n",
      "    label: \"What is the name of the adgroup associated with this event?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.adgroup_name ;;\n",
      "  }\n",
      "\n",
      "  dimension: campaign_id {\n",
      "    label: \"What is the unique identifier for the campaign associated with this event?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.campaign_id ;;\n",
      "  }\n",
      "\n",
      "  dimension: campaign_name {\n",
      "    label: \"What is the name of the campaign associated with this event?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.campaign_name ;;\n",
      "  }\n",
      "\n",
      "  dimension_group: clicked_at {\n",
      "    label: \"When did the visitor click the ad?\"\n",
      "    type: time\n",
      "    datatype: datetime\n",
      "    sql: ${TABLE}.clicked_at ;;\n",
      "  }\n",
      "\n",
      "  dimension_group: converted_at {\n",
      "    label: \"When was the ad converted?\"\n",
      "    type: time\n",
      "    datatype: datetime\n",
      "    sql: ${TABLE}.converted_at ;;\n",
      "  }\n",
      "\n",
      "  dimension: creative_id {\n",
      "    label: \"What is the unique identifier for the creative associated with this event?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.creative_id ;;\n",
      "  }\n",
      "\n",
      "  dimension: creative_name {\n",
      "    label: \"What is the name of the creative associated with this event?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.creative_name ;;\n",
      "  }\n",
      "\n",
      "  dimension: keyword {\n",
      "    label: \"What is the keyword associated with this event?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.keyword ;;\n",
      "  }\n",
      "\n",
      "  dimension: lineitem_id {\n",
      "    label: \"What is the unique identifier for the lineitem associated with this event?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.lineitem_id ;;\n",
      "  }\n",
      "\n",
      "  dimension: lineitem_name {\n",
      "    label: \"What is the name of the lineitem associated with this event?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.lineitem_name ;;\n",
      "  }\n",
      "\n",
      "  dimension: org_name {\n",
      "    label: \"What is the name of the org associated with this event?\"\n",
      "    type: string\n",
      "    sql: ${TABLE}.org_name ;;\n",
      "  }\n",
      "}\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from io import StringIO\n",
    "f = StringIO()\n",
    "\n",
    "ad_attributions = get_table_metadata('ad_attributions')\n",
    "generate_lookml(ad_attributions, f)\n",
    "print(f.getvalue())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}