elasticsearch.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To try executing queries against a \"real\" Elasticsearch database, we first require Calcite as well as the tests for the Elasticsearch adapter which contain some useful code to get us started."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%loadFromPOM\n",
    "<dependency>\n",
    "    <groupId>org.apache.calcite</groupId>\n",
    "    <artifactId>calcite-core</artifactId>\n",
    "    <version>1.21.0</version>\n",
    "</dependency>\n",
    "<dependency>\n",
    "    <groupId>org.apache.calcite</groupId>\n",
    "    <artifactId>calcite-elasticsearch</artifactId>\n",
    "    <version>1.21.0</version>\n",
    "</dependency>\n",
    "<dependency>\n",
    "    <groupId>org.elasticsearch</groupId>\n",
    "    <artifactId>elasticsearch</artifactId>\n",
    "    <version>7.0.1</version>\n",
    "</dependency>\n",
    "<dependency>\n",
    "    <groupId>org.elasticsearch.plugin</groupId>\n",
    "    <artifactId>transport-netty4-client</artifactId>\n",
    "    <version>7.0.1</version>\n",
    "</dependency>\n",
    "<dependency>\n",
    "    <groupId>org.codelibs.elasticsearch.module</groupId>\n",
    "    <artifactId>lang-painless</artifactId>\n",
    "    <version>7.0.1</version>\n",
    "</dependency>\n",
    "<dependency>\n",
    "    <groupId>org.apache.calcite</groupId>\n",
    "    <artifactId>calcite-elasticsearch</artifactId>\n",
    "    <version>1.21.0</version>\n",
    "    <classifier>tests</classifier>\n",
    "    <type>test-jar</type>\n",
    "</dependency>\n",
    "<dependency>\n",
    "    <groupId>org.apache.calcite</groupId>\n",
    "    <artifactId>calcite-core</artifactId>\n",
    "    <version>1.21.0</version>\n",
    "    <classifier>tests</classifier>\n",
    "    <type>test-jar</type>\n",
    "</dependency>\n",
    "<dependency>\n",
    "    <groupId>junit</groupId>\n",
    "    <artifactId>junit</artifactId>\n",
    "    <version>4.12</version>\n",
    "</dependency>\n",
    "<dependency>\n",
    "    <groupId>org.apache.logging.log4j</groupId>\n",
    "    <artifactId>log4j-core</artifactId>\n",
    "    <version>2.11.0</version>\n",
    "</dependency>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we construct an instance of `HrSchema` using `CalciteAssert` which is also from Calcite's testing libraries.\n",
    "In practice, you would connect to a real database or at least some source of schema information.\n",
    "With the schema, we first construct a [`RelBuilder`](https://calcite.apache.org/apidocs/org/apache/calcite/tools/RelBuilder.html) that we can use to build up a tree of relational algebra operators."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loaded schema\n"
     ]
    }
   ],
   "source": [
    "import org.apache.calcite.adapter.elasticsearch.ElasticSearchAdapterTest;\n",
    "import org.apache.calcite.adapter.elasticsearch.ElasticsearchSchema;\n",
    "import org.apache.calcite.jdbc.CalciteConnection;\n",
    "import org.apache.calcite.schema.SchemaPlus;\n",
    "\n",
    "import com.fasterxml.jackson.databind.ObjectMapper;\n",
    "import org.elasticsearch.client.RestClient;\n",
    "\n",
    "import java.lang.reflect.Field;\n",
    "import java.lang.reflect.InvocationTargetException;\n",
    "import java.lang.reflect.Method;\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "\n",
    "// Normally we would use a JSON model file, but there area bunch of\n",
    "// hacks here so we can use an embedded Elasticsearch instance instead\n",
    "// See the docs below for more information:\n",
    "// https://calcite.apache.org/docs/elasticsearch_adapter.html\n",
    "Connection connection = DriverManager.getConnection(\"jdbc:calcite:lex=JAVA\");\n",
    "SchemaPlus root = connection.unwrap(CalciteConnection.class).getRootSchema();\n",
    "\n",
    "// Since the class we need is not exposed by tests,\n",
    "// we need some reflection hackery to get it\n",
    "// This is *not* how the adapter should be used,\n",
    "// but it is useful for demonstration\n",
    "Object policy = ElasticSearchAdapterTest.NODE;\n",
    "Field field = policy.getClass().getDeclaredField(\"node\");\n",
    "field.setAccessible(true);\n",
    "Object node = field.get(policy);\n",
    "\n",
    "// Start the embedded node\n",
    "Method method = node.getClass().getMethod(\"start\");\n",
    "method.setAccessible(true);\n",
    "method.invoke(node);\n",
    "ElasticSearchAdapterTest.setupInstance();\n",
    "\n",
    "// Get the REST client\n",
    "Method restClientMethod = policy.getClass().getDeclaredMethod(\"restClient\");\n",
    "restClientMethod.setAccessible(true);\n",
    "RestClient restClient = (RestClient) restClientMethod.invoke(policy);\n",
    "\n",
    "// Get the object mapper\n",
    "Method mapperMethod = policy.getClass().getDeclaredMethod(\"mapper\");\n",
    "mapperMethod.setAccessible(true);\n",
    "ObjectMapper mapper = (ObjectMapper) mapperMethod.invoke(policy);\n",
    "\n",
    "// Create a new schema\n",
    "root.add(\"elastic\", new ElasticsearchSchema(restClient, mapper, \"zips\"));\n",
    "System.out.println(\"Loaded schema\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now access Elasticsearch and execute SQL queries.\n",
    "Note from the output that Calcite does not attempt to do any sort of schema inference.\n",
    "The schema calcite exposes contains a single column named `_MAP` which is of the SQL map type and contains all the data in the document."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "_MAP={id=83704, city=BOISE, loc=[-116.295099, 43.633001], pop=40912, state=ID}\n"
     ]
    }
   ],
   "source": [
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "\n",
    "Statement statement = connection.createStatement();\n",
    "ResultSet resultSet = statement.executeQuery(\"SELECT * FROM `elastic`.`zips` WHERE _MAP['id']=83704\");\n",
    "\n",
    "ResultSetMetaData rsmd = resultSet.getMetaData();\n",
    "int columnsNumber = rsmd.getColumnCount();\n",
    "while (resultSet.next()) {\n",
    "    for (int i = 1; i <= columnsNumber; i++) {\n",
    "        if (i > 1) System.out.print(\",  \");\n",
    "        String columnValue = resultSet.getString(i);\n",
    "        System.out.print(rsmd.getColumnName(i) + \"=\" + columnValue);\n",
    "    }\n",
    "    System.out.println(\"\");\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While we can write queries in this form by accessing fields in the map such as `_MAP['city']`, this is not very convenient.\n",
    "Calcite also does not know the type of each of these fields are retrieved.\n",
    "We can make it much easier to deal with schema by defining a view.\n",
    "Here we add the view programmatically, but views can also be [defined in the model JSON](https://calcite.apache.org/docs/model.html#view)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import org.apache.calcite.schema.impl.ViewTable;\n",
    "import org.apache.calcite.schema.impl.ViewTableMacro;\n",
    "\n",
    "final String viewSql = \"select cast(_MAP['city'] AS varchar(20)) AS \\\"city\\\", \"\n",
    "  + \"cast(_MAP['loc'][0] AS float) AS \\\"longitude\\\",\\n\"\n",
    "  + \"cast(_MAP['loc'][1] AS float) AS \\\"latitude\\\",\\n\"\n",
    "  + \"cast(_MAP['pop'] AS integer) AS \\\"pop\\\", \"\n",
    "  + \"cast(_MAP['state'] AS varchar(2)) AS \\\"state\\\", \"\n",
    "  + \"cast(_MAP['id'] AS varchar(5)) AS \\\"id\\\" \"\n",
    "  + \"from \\\"elastic\\\".\\\"zips\\\"\";\n",
    "\n",
    "ViewTableMacro macro = ViewTable.viewMacro(root, viewSql,\n",
    "  Collections.singletonList(\"elastic\"), Arrays.asList(\"elastic\", \"view\"), false);\n",
    "root.add(\"zips\", macro);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can query the schema using this view and see all the columns defined in the view included.\n",
    "While it is not directly shown, the types of these columns are also correctly defined."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "city=ABERDEEN,  longitude=-98.485642,  latitude=45.466109,  pop=28786,  state=SD,  id=57401\n",
      "city=AIKEN,  longitude=-81.719429,  latitude=33.553024,  pop=51233,  state=SC,  id=29801\n",
      "city=ALTON,  longitude=-98.342647,  latitude=26.24153,  pop=67604,  state=TX,  id=78572\n",
      "city=AMES,  longitude=-93.639398,  latitude=42.029859,  pop=52105,  state=IA,  id=50010\n",
      "city=ANCHORAGE,  longitude=-149.74467,  latitude=61.203696,  pop=32383,  state=AK,  id=99504\n",
      "city=BALTIMORE,  longitude=-76.679397,  latitude=39.344572,  pop=74402,  state=MD,  id=21215\n",
      "city=BANGOR,  longitude=-68.791839,  latitude=44.824199,  pop=40434,  state=ME,  id=04401\n",
      "city=BAVARIA,  longitude=-97.608787,  latitude=38.823802,  pop=45208,  state=KS,  id=67401\n",
      "city=BAYONNE,  longitude=-74.119169,  latitude=40.666399,  pop=61444,  state=NJ,  id=07002\n",
      "city=BEAVERTON,  longitude=-122.805395,  latitude=45.475035,  pop=46660,  state=OR,  id=97005\n",
      "city=BECKLEY,  longitude=-81.206084,  latitude=37.793214,  pop=45196,  state=WV,  id=25801\n",
      "city=BELL GARDENS,  longitude=-118.17205,  latitude=33.969177,  pop=99568,  state=CA,  id=90201\n",
      "city=BILLINGS,  longitude=-108.572662,  latitude=45.781265,  pop=40121,  state=MT,  id=59102\n",
      "city=BISMARCK,  longitude=-100.774755,  latitude=46.823448,  pop=36602,  state=ND,  id=58501\n",
      "city=BOISE,  longitude=-116.295099,  latitude=43.633001,  pop=40912,  state=ID,  id=83704\n",
      "city=BOW MAR,  longitude=-105.07766,  latitude=39.596854,  pop=59418,  state=CO,  id=80123\n",
      "city=BRATTLEBORO,  longitude=-72.593322,  latitude=42.857353,  pop=17522,  state=VT,  id=05301\n",
      "city=BREMERTON,  longitude=-122.629913,  latitude=47.601916,  pop=49057,  state=WA,  id=98310\n",
      "city=BRISTOL,  longitude=-72.930193,  latitude=41.682293,  pop=60670,  state=CT,  id=06010\n",
      "city=BROCKTON,  longitude=-71.034348,  latitude=42.081571,  pop=59498,  state=MA,  id=02401\n",
      "city=BROOKLYN,  longitude=-73.956985,  latitude=40.646694,  pop=111396,  state=NY,  id=11226\n",
      "city=BROWNSVILLE,  longitude=-97.461236,  latitude=25.922103,  pop=79463,  state=TX,  id=78521\n",
      "city=BURLINGTON,  longitude=-73.219875,  latitude=44.484023,  pop=39127,  state=VT,  id=05401\n",
      "city=BURNSVILLE,  longitude=-93.275283,  latitude=44.76086,  pop=51421,  state=MN,  id=55337\n",
      "city=CENTER POINT,  longitude=-86.693197,  latitude=33.635447,  pop=43862,  state=AL,  id=35215\n",
      "city=CHARLOTTESVILLE,  longitude=-78.490869,  latitude=38.054752,  pop=62708,  state=VA,  id=22901\n",
      "city=CHEYENNE,  longitude=-104.796234,  latitude=41.143719,  pop=33107,  state=WY,  id=82001\n",
      "city=CHICAGO,  longitude=-87.7157,  latitude=41.849015,  pop=112047,  state=IL,  id=60623\n",
      "city=CICERO,  longitude=-87.76008,  latitude=41.84776,  pop=67670,  state=IL,  id=60650\n",
      "city=CLARKSVILLE,  longitude=-87.418621,  latitude=36.585315,  pop=43296,  state=TN,  id=37042\n",
      "city=CLEONA,  longitude=-76.425895,  latitude=40.335912,  pop=61993,  state=PA,  id=17042\n",
      "city=COEUR D ALENE,  longitude=-116.784976,  latitude=47.692841,  pop=33589,  state=ID,  id=83814\n",
      "city=CONCORD,  longitude=-71.527734,  latitude=43.218525,  pop=34035,  state=NH,  id=03301\n",
      "city=CONWAY,  longitude=-92.423574,  latitude=35.084199,  pop=43236,  state=AR,  id=72032\n",
      "city=DECATUR,  longitude=-84.263165,  latitude=33.740825,  pop=56056,  state=GA,  id=30032\n",
      "city=DETROIT,  longitude=-83.193732,  latitude=42.388303,  pop=68390,  state=MI,  id=48227\n",
      "city=DOVER,  longitude=-75.535983,  latitude=39.156639,  pop=46005,  state=DE,  id=19901\n",
      "city=DUBUQUE,  longitude=-90.681914,  latitude=42.514977,  pop=41934,  state=IA,  id=52001\n",
      "city=EDGEWATER,  longitude=-81.797143,  latitude=41.482654,  pop=59702,  state=OH,  id=44107\n",
      "city=EDMOND,  longitude=-97.479835,  latitude=35.666483,  pop=43814,  state=OK,  id=73034\n",
      "city=ELM GROVE,  longitude=-80.685126,  latitude=40.072736,  pop=49136,  state=WV,  id=26003\n",
      "city=ELYRIA,  longitude=-82.105088,  latitude=41.372353,  pop=66674,  state=OH,  id=44035\n",
      "city=FAIRBANKS,  longitude=-147.846917,  latitude=64.85437,  pop=23238,  state=AK,  id=99709\n",
      "city=FARGO,  longitude=-96.812252,  latitude=46.856406,  pop=38483,  state=ND,  id=58103\n",
      "city=FEDERAL HEIGHTS,  longitude=-105.007985,  latitude=39.840562,  pop=54069,  state=CO,  id=80221\n",
      "city=FLORENCE,  longitude=-79.772786,  latitude=34.18375,  pop=66990,  state=SC,  id=29501\n",
      "city=FLORISSANT,  longitude=-90.340097,  latitude=38.806865,  pop=52659,  state=MO,  id=63031\n",
      "city=FORKS TOWNSHIP,  longitude=-75.23582,  latitude=40.6867,  pop=65784,  state=PA,  id=18042\n",
      "city=FORT LAUDERDALE,  longitude=-80.172786,  latitude=26.142104,  pop=65378,  state=FL,  id=33311\n",
      "city=FRAMINGHAM,  longitude=-71.425486,  latitude=42.300665,  pop=65046,  state=MA,  id=01701\n",
      "city=GARDEN CITY,  longitude=-104.704756,  latitude=40.413968,  pop=53905,  state=CO,  id=80631\n",
      "city=GILLETTE,  longitude=-105.497442,  latitude=44.282009,  pop=25968,  state=WY,  id=82716\n",
      "city=GLEN BURNIE,  longitude=-76.618862,  latitude=39.158968,  pop=75692,  state=MD,  id=21061\n",
      "city=GRAVEL RIDGE,  longitude=-92.130435,  latitude=34.881985,  pop=37428,  state=AR,  id=72076\n",
      "city=GWYNN OAK,  longitude=-76.734064,  latitude=39.329628,  pop=76002,  state=MD,  id=21207\n",
      "city=HATTIESBURG,  longitude=-89.306471,  latitude=31.314553,  pop=41866,  state=MS,  id=39401\n",
      "city=HATTON,  longitude=-84.88061,  latitude=38.192831,  pop=46563,  state=KY,  id=40601\n",
      "city=HELENA,  longitude=-112.021283,  latitude=46.613066,  pop=40102,  state=MT,  id=59601\n",
      "city=HIALEAH,  longitude=-80.3059,  latitude=25.865395,  pop=73194,  state=FL,  id=33012\n",
      "city=HONOLULU,  longitude=-157.926925,  latitude=21.353173,  pop=62915,  state=HI,  id=96818\n",
      "city=IRVINGTON,  longitude=-74.231271,  latitude=40.7261,  pop=60986,  state=NJ,  id=07111\n",
      "city=JACKSON HEIGHTS,  longitude=-73.878551,  latitude=40.740388,  pop=88241,  state=NY,  id=11373\n",
      "city=JACKSONVILLE,  longitude=-81.547387,  latitude=30.293907,  pop=58867,  state=FL,  id=32216\n",
      "city=JEFFERSONVILLE,  longitude=-85.735885,  latitude=38.307767,  pop=56543,  state=IN,  id=47130\n",
      "city=JENNINGS,  longitude=-90.260189,  latitude=38.738878,  pop=54994,  state=MO,  id=63136\n",
      "city=JONESBORO,  longitude=-90.696526,  latitude=35.833016,  pop=53532,  state=AR,  id=72401\n",
      "city=JUNEAU,  longitude=-134.529429,  latitude=58.362767,  pop=24947,  state=AK,  id=99801\n",
      "city=KAILUA,  longitude=-157.744781,  latitude=21.406262,  pop=53403,  state=HI,  id=96734\n",
      "city=KANEOHE,  longitude=-157.811543,  latitude=21.422819,  pop=55236,  state=HI,  id=96744\n",
      "city=KEARNEY,  longitude=-99.077883,  latitude=40.713608,  pop=28674,  state=NE,  id=68847\n",
      "city=KEARNS,  longitude=-111.98521,  latitude=40.652759,  pop=55999,  state=UT,  id=84118\n",
      "city=KENT,  longitude=-122.193184,  latitude=47.388004,  pop=50515,  state=WA,  id=98031\n",
      "city=LARAMIE,  longitude=-105.581146,  latitude=41.312907,  pop=29327,  state=WY,  id=82070\n",
      "city=LAS CRUCES,  longitude=-106.746034,  latitude=32.321641,  pop=57502,  state=NM,  id=88001\n",
      "city=LAS VEGAS,  longitude=-115.067062,  latitude=36.215818,  pop=51532,  state=NV,  id=89115\n",
      "city=LAUREL,  longitude=-89.131155,  latitude=31.705444,  pop=45040,  state=MS,  id=39440\n",
      "city=LAWTON,  longitude=-98.455234,  latitude=34.617939,  pop=45542,  state=OK,  id=73505\n",
      "city=LEWISTON,  longitude=-70.191619,  latitude=44.098538,  pop=40173,  state=ME,  id=04240\n",
      "city=LEXINGTON,  longitude=-80.262049,  latitude=35.82306,  pop=69179,  state=NC,  id=27292\n",
      "city=LINCOLN,  longitude=-96.693763,  latitude=40.789282,  pop=27576,  state=NE,  id=68502\n",
      "city=LOS ANGELES,  longitude=-118.258189,  latitude=34.007856,  pop=96074,  state=CA,  id=90011\n",
      "city=LOUISVILLE,  longitude=-85.778027,  latitude=38.159318,  pop=42198,  state=KY,  id=40214\n",
      "city=MANCHESTER,  longitude=-71.449325,  latitude=42.965563,  pop=36613,  state=NH,  id=03103\n",
      "city=MANHATTAN,  longitude=-96.585776,  latitude=39.193757,  pop=50178,  state=KS,  id=66502\n",
      "city=MANOR,  longitude=-75.589938,  latitude=39.67703,  pop=46906,  state=DE,  id=19720\n",
      "city=MARRERO,  longitude=-90.110462,  latitude=29.859756,  pop=58905,  state=LA,  id=70072\n",
      "city=MEMPHIS,  longitude=-90.073238,  latitude=35.042538,  pop=60508,  state=TN,  id=38109\n",
      "city=MENTOR,  longitude=-81.342133,  latitude=41.689468,  pop=60109,  state=OH,  id=44060\n",
      "city=MERIDEN,  longitude=-72.799734,  latitude=41.533396,  pop=59441,  state=CT,  id=06450\n",
      "city=MESA,  longitude=-111.789554,  latitude=33.399168,  pop=55180,  state=AZ,  id=85204\n",
      "city=MICHIGAN CITY,  longitude=-86.869899,  latitude=41.698031,  pop=55392,  state=IN,  id=46360\n",
      "city=MIDDLETOWN,  longitude=-71.30348,  latitude=41.504502,  pop=47687,  state=RI,  id=02840\n",
      "city=MILWAUKEE,  longitude=-87.947834,  latitude=43.118765,  pop=51008,  state=WI,  id=53209\n",
      "city=MINOT,  longitude=-101.298476,  latitude=48.22914,  pop=42195,  state=ND,  id=58701\n",
      "city=MISSOULA,  longitude=-114.025207,  latitude=46.856274,  pop=33811,  state=MT,  id=59801\n",
      "city=NASHUA,  longitude=-71.466684,  latitude=42.756395,  pop=41438,  state=NH,  id=03060\n",
      "city=NASHVILLE,  longitude=-86.724038,  latitude=36.072486,  pop=51478,  state=TN,  id=37211\n",
      "city=NEW BRIGHTON,  longitude=-93.199691,  latitude=45.074129,  pop=44128,  state=MN,  id=55112\n",
      "city=NEW IBERIA,  longitude=-91.819959,  latitude=30.001027,  pop=56105,  state=LA,  id=70560\n",
      "city=NEW ORLEANS,  longitude=-90.03124,  latitude=29.970298,  pop=56494,  state=LA,  id=70117\n",
      "city=NEW YORK,  longitude=-73.958805,  latitude=40.768476,  pop=106564,  state=NY,  id=10021\n",
      "city=NEWARK,  longitude=-75.737534,  latitude=39.701129,  pop=50573,  state=DE,  id=19711\n",
      "city=NEWPORT NEWS,  longitude=-76.532125,  latitude=37.131684,  pop=68525,  state=VA,  id=23602\n",
      "city=NORRIDGE,  longitude=-87.796054,  latitude=41.945213,  pop=69160,  state=IL,  id=60634\n",
      "city=NORTH LAS VEGAS,  longitude=-115.124832,  latitude=36.206228,  pop=48113,  state=NV,  id=89031\n",
      "city=NORTH SMITHFIELD,  longitude=-71.513683,  latitude=41.99948,  pop=53733,  state=RI,  id=02895\n",
      "city=NORTH WALTHAM,  longitude=-71.236497,  latitude=42.382492,  pop=57871,  state=MA,  id=02154\n",
      "city=NORWALK,  longitude=-118.081767,  latitude=33.90564,  pop=94188,  state=CA,  id=90650\n",
      "city=OMAHA,  longitude=-95.999888,  latitude=41.29186,  pop=35325,  state=NE,  id=68104\n",
      "city=OSHKOSH,  longitude=-88.543635,  latitude=44.021962,  pop=57187,  state=WI,  id=54901\n",
      "city=OSWEGO,  longitude=-80.321008,  latitude=33.928199,  pop=46394,  state=SC,  id=29150\n",
      "city=PAWTUCKET,  longitude=-71.390713,  latitude=41.872873,  pop=45442,  state=RI,  id=02860\n",
      "city=PEACH ORCHARD,  longitude=-82.038358,  latitude=33.402024,  pop=58646,  state=GA,  id=30906\n",
      "city=PENTAGON,  longitude=-77.038196,  latitude=38.891019,  pop=21,  state=DC,  id=20301\n",
      "city=PHILADELPHIA,  longitude=-75.228819,  latitude=39.944815,  pop=80454,  state=PA,  id=19143\n",
      "city=PHOENIX,  longitude=-112.111838,  latitude=33.632383,  pop=54668,  state=AZ,  id=85023\n",
      "city=PLEASANT HILL,  longitude=-93.549446,  latitude=41.612499,  pop=39883,  state=IA,  id=50317\n",
      "city=POJOAQUE VALLEY,  longitude=-105.974818,  latitude=35.702472,  pop=51715,  state=NM,  id=87501\n",
      "city=PORTLAND,  longitude=-122.59727,  latitude=45.483995,  pop=43134,  state=OR,  id=97206\n",
      "city=PROVO,  longitude=-111.654906,  latitude=40.260681,  pop=43841,  state=UT,  id=84604\n",
      "city=RENO,  longitude=-119.776395,  latitude=39.497239,  pop=38332,  state=NV,  id=89502\n",
      "city=RENTON,  longitude=-122.151178,  latitude=47.467383,  pop=48197,  state=WA,  id=98059\n",
      "city=ROCKERVILLE,  longitude=-103.200259,  latitude=44.077041,  pop=45328,  state=SD,  id=57701\n",
      "city=ROSWELL,  longitude=-104.525857,  latitude=33.388504,  pop=53644,  state=NM,  id=88201\n",
      "city=RUTLAND,  longitude=-72.970773,  latitude=43.614131,  pop=22576,  state=VT,  id=05701\n",
      "city=SAINT LOUIS,  longitude=-90.262543,  latitude=38.581356,  pop=49014,  state=MO,  id=63116\n",
      "city=SAINT PAUL,  longitude=-93.048817,  latitude=44.968384,  pop=47905,  state=MN,  id=55106\n",
      "city=SALEM,  longitude=-122.979692,  latitude=44.926039,  pop=48007,  state=OR,  id=97301\n",
      "city=SAN ANTONIO,  longitude=-98.525967,  latitude=29.422855,  pop=58355,  state=TX,  id=78207\n",
      "city=SHAWNEE,  longitude=-96.931321,  latitude=35.34907,  pop=40076,  state=OK,  id=74801\n",
      "city=SHIVELY,  longitude=-85.831771,  latitude=38.186138,  pop=41719,  state=KY,  id=40216\n",
      "city=SIOUX FALLS,  longitude=-96.686415,  latitude=43.537386,  pop=32508,  state=SD,  id=57103\n",
      "city=SOUTHSIDE,  longitude=-86.010279,  latitude=33.997248,  pop=44165,  state=AL,  id=35901\n",
      "city=STAMFORD,  longitude=-73.537428,  latitude=41.052552,  pop=54605,  state=CT,  id=06902\n",
      "city=STAR CITY,  longitude=-79.954225,  latitude=39.633858,  pop=70185,  state=WV,  id=26505\n",
      "city=STATESVILLE,  longitude=-80.894009,  latitude=35.799022,  pop=52895,  state=NC,  id=28677\n",
      "city=TAYLOR,  longitude=-83.267269,  latitude=42.231738,  pop=70811,  state=MI,  id=48180\n",
      "city=TUSCALOOSA,  longitude=-87.562666,  latitude=33.196891,  pop=42124,  state=AL,  id=35401\n",
      "city=TWIN FALLS,  longitude=-114.469265,  latitude=42.556495,  pop=34539,  state=ID,  id=83301\n",
      "city=VICKSBURG,  longitude=-90.85065,  latitude=32.325824,  pop=46968,  state=MS,  id=39180\n",
      "city=VIRGINIA BEACH,  longitude=-76.175909,  latitude=36.797772,  pop=67276,  state=VA,  id=23464\n",
      "city=WASHINGTON,  longitude=-77.020251,  latitude=38.951786,  pop=62924,  state=DC,  id=20011\n",
      "city=WAUSAU,  longitude=-89.633955,  latitude=44.963433,  pop=51083,  state=WI,  id=54401\n",
      "city=WEEHAWKEN,  longitude=-74.030558,  latitude=40.768153,  pop=69646,  state=NJ,  id=07087\n",
      "city=WEST LAFAYETTE,  longitude=-86.923661,  latitude=40.444025,  pop=54702,  state=IN,  id=47906\n",
      "city=WEST VALLEY CITY,  longitude=-112.009783,  latitude=40.68708,  pop=52854,  state=UT,  id=84120\n",
      "city=WESTLAND,  longitude=-83.374908,  latitude=42.318882,  pop=84712,  state=MI,  id=48185\n",
      "city=WICHITA,  longitude=-97.438344,  latitude=37.700683,  pop=41349,  state=KS,  id=67212\n",
      "city=YUMA,  longitude=-114.642362,  latitude=32.701507,  pop=57131,  state=AZ,  id=85364\n"
     ]
    }
   ],
   "source": [
    "Statement statement = connection.createStatement();\n",
    "ResultSet resultSet = statement.executeQuery(\"SELECT * FROM `zips` ORDER BY `city`, `state`\");\n",
    "\n",
    "ResultSetMetaData rsmd = resultSet.getMetaData();\n",
    "int columnsNumber = rsmd.getColumnCount();\n",
    "while (resultSet.next()) {\n",
    "    for (int i = 1; i <= columnsNumber; i++) {\n",
    "        if (i > 1) System.out.print(\",  \");\n",
    "        String columnValue = resultSet.getString(i);\n",
    "        System.out.print(rsmd.getColumnName(i) + \"=\" + columnValue);\n",
    "    }\n",
    "    System.out.println(\"\");\n",
    "}"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Java",
   "language": "java",
   "name": "java"
  },
  "language_info": {
   "codemirror_mode": "java",
   "file_extension": ".java",
   "mimetype": "text/x-java-source",
   "name": "Java",
   "pygments_lexer": "java",
   "version": "11.0.1+13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}