Generate LookML.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-20T23:14:13.989876Z",
     "start_time": "2017-06-20T23:14:13.982669Z"
    }
   },
   "outputs": [],
   "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"
    },
    "collapsed": true
   },
   "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
}