I am a caffeinated, busy software junkie. Daily I help teams with solution engineering solutions

Daily I help teams with solution engineering aspect of connected vehicle data projects. (massive datasets & always some new datasets with new car models aka new technologies.) Lately in the spare time, applying some of the ML/Deep learning techniques on datasets (many are create based on observations of real datasets)To Share some thoughts on my work (main half of this blog) and the other half will be about my family and friends.

Wednesday, March 05, 2014

SOLR Velocity Template based web UI to database dictionary aka database walker

Here primary use case is, in most of the large IT organizations, lot’s of internal IT applications uses some kind of RDBMS as back-end and over the years, one will see hundred of databases. Again typical SOLO style operations i.e. one department focus on their needs only. In general one will end up seeing lot’s of duplication of data. However In my case, I end up analyzing very large database (hundred of schema or tables spaces and thousands of tables and 5 digit number of columns.) views, materialized views, stored procedures and many more. I noticed lot’s of people are using Oracle SQL developer for analysis and keep jumping from one table or view to other tables in other schemas. After seeing this, I wrote a small database walker. Primary purpose is to crawls entire Oracle data dictionary and produces some xml. I am feeding this to SOLR so that I can build simple Google kind of interface with using SOLRs default velocity templates based web UI to search for tables or columns or schemas or primary key and many more. I will host entire project in the GIT hub. In this post, I am including Oracle Table metadata only. (i.e. table columns, primary key, import & exported keys and column meta data etc. I wrote more code to pull stored procedures code etc.)

public static void  tableInfo(DatabaseMetaData meta,String tableName,String tableType, String schemaName) throws Exception {

  String   catalog   = null;
  String   schemaPattern     = schemaName;
  String   tableNamePattern  = tableName;
  String   columnNamePattern = null;

  String outputFile  = stageDir+schemaName+"_"+tableName+".xml";

  File f  = new File(outputFile);
  if (f.exists()){
  System.out.print("Skiping->"+outputFile);
  return;
  }

  FileWriter fw = null;
  try{
   fw = new FileWriter(outputFile);
  }catch(Exception e){
  System.out.print("Error ...  Skiping->"+outputFile);
  return;
  }

  if (fw == null){
   System.out.print("Unable to open file.  Skiping->"+outputFile);
   return;
  }

  fw.write("<add>\n");
  fw.write("<doc>\n");

  ResultSet result = meta.getColumns(
      catalog, schemaPattern,  tableNamePattern, columnNamePattern);

   String colName = "field name=\""+"id"+"\"";
   fw.write("<" + colName + ">");
   fw.write(tableName);
   fw.write("</field>");
   fw.write( "\n");

   colName = "field name=\""+"tableName"+"\"";
   fw.write("<" + colName + ">");
   fw.write(tableName);
   fw.write("</field>");
   fw.write( "\n");

   colName = "field name=\""+"tableType"+"\"";
   fw.write("<" + colName + ">");
   fw.write(tableType);
   fw.write("</field>");
   fw.write( "\n");

   colName = "field name=\""+"schemaName"+"\"";
   fw.write("<" + colName + ">");
   fw.write(schemaName);
   fw.write("</field>");
   fw.write( "\n");

  while(result.next()){///TODO remove 2,3,3 junk
      String columnName = result.getString(4);
      int    columnType = result.getInt(5);
      String columnTypeStr = result.getString(6);
      String catName = result.getString(1);

   colName = "field name=\""+"colName"+"\"";
   fw.write("<" + colName + ">");
   fw.write(columnName);
   fw.write("</field>");
   fw.write( "\n");

   //colName = "field name=\""+columnName+"_dtype"+"\"";
  // fw.write("<" + colName + ">");
   //fw.write(columnTypeStr);
  // fw.write("</field>");
   //fw.write( "\n");

   colName = "field name=\""+"colMeta"+"\"";
   fw.write("<" + colName + ">");
   fw.write(columnName+","+columnTypeStr);
   fw.write("</field>");
   fw.write( "\n");

  ////pull logical data.
  String[] logicalData = LogicalMetadata.getLogicalData(schemaName, tableName,columnName);
  if ( logicalData != null && logicalData.length <=7){
  String entityName = logicalData[3];
  String entityAttrName = logicalData[4];
  String entityAttrDesc = logicalData[6];

  /*
   colName = "field name=\""+"logicalEntityName"+"\"";
   fw.write("<" + colName + ">");
   fw.write(entityName);
   fw.write("</field>");
   fw.write( "\n");*/

   String logicalName = columnName+ "_lan";

   colName = "field name=\""+logicalName+"\"";
   fw.write("<" + colName + ">");
   fw.write(entityAttrName);
   fw.write("</field>");
   fw.write( "\n");

   String logicalDesc = columnName+ "_lad";

   colName = "field name=\""+logicalDesc+"\"";
   fw.write("<" + colName + ">");
   fw.write(entityAttrDesc);
   fw.write("</field>");
   fw.write( "\n");

  }
  }
  result.close();

  ResultSet  result1 = meta.getPrimaryKeys(
    catalog, schemaName, tableNamePattern);
  String columnName = null;
  HashSet set = new HashSet();

  while(result1.next()){
  columnName = result1.getString(4);
   if (set.contains(columnName)){
   //do nothing
   }else{
   colName = "field name=\""+"primaryKey"+"\"";
   fw.write("<" + colName + ">");
   fw.write(columnName);
   fw.write("</field>");
   fw.write( "\n");
   set.add(columnName);
   //System.out.println(" primary key" + columnName);
   }
  }
  result1.close();

  /////
  set.clear();

    ResultSet rs = meta.getExportedKeys(
            catalog, schemaPattern, tableNamePattern );

        while (rs.next()) {
            String fkTableName = rs.getString("FKTABLE_NAME");
            String fkColumnName = rs.getString("FKCOLUMN_NAME");
            int fkSequence = rs.getInt("KEY_SEQ");

   colName = "field name=\""+"ExportedKeys_Table_Colum_Seq"+"\"";
   fw.write("<" + colName + ">");
   fw.write(fkTableName+"."+fkColumnName+"."+fkSequence);
   fw.write("</field>");
   fw.write( "\n");

         }
        rs.close();

        ResultSet foreignKeys = meta.getImportedKeys( catalog, schemaName, tableNamePattern);
        while (foreignKeys.next()) {
            String fkTableName = foreignKeys.getString("FKTABLE_NAME");
            String fkColumnName = foreignKeys.getString("FKCOLUMN_NAME");
            String pkTableName = foreignKeys.getString("PKTABLE_NAME");
            String pkColumnName = foreignKeys.getString("PKCOLUMN_NAME");
   colName = "field name=\""+"ImportedKeys_Table_Colum_Seq"+"\"";
   fw.write("<" + colName + ">");
   fw.write(fkTableName+"."+fkColumnName+"."+pkTableName+"."+pkColumnName);
   fw.write("</field>");
   fw.write( "\n");
        }
        foreignKeys.close();

  fw.write("</doc>\n");
  fw.write("</add>\n");

  fw.flush();
  fw.close();

  }

Monday, March 03, 2014

Very Old: XSLT, XSL code convert XML file to produce SOLR documents ( aka xml)

Based on content needs, I used different strategies to produce SOLR input documents.
One of old post, contains Excel input to SOLR input documents.
In this particular use-case, a simple XSL aka XSLT, style-sheet converts an incoming eCommerce XML content feed in to SOLR documents & pushes these documents to SOLR staging folder.
From there, our custom SOLR component consumes these xml to add or update new content at regular intervals. I will post that custom SOLR component logic later in this blog. For now, I am including sample stylesheet which produces solr input XML document based another XML input feed

<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

  <xsl:strip-space elements="*"/>
  <xsl:output method="xml" />

  <xsl:template match="/products">
     <add>
         <xsl:apply-templates />
     </add>
  </xsl:template>

<xsl:template match="product">
     <doc>
       <xsl:element name="field">
            <xsl:attribute name="name">id</xsl:attribute>
            <xsl:value-of select="./@skuNo"/>
        </xsl:element>

       <xsl:element name="field">
            <xsl:attribute name="name">status</xsl:attribute>
            <xsl:value-of select="./@status"/>
        </xsl:element>
       <xsl:element name="field">
            <xsl:attribute name="name">brand</xsl:attribute>
            <xsl:value-of select="./brand"/>
        </xsl:element>
       <xsl:element name="field">
            <xsl:attribute name="name">description</xsl:attribute>
            <xsl:value-of select="./descriptions/description"/>
        </xsl:element>
       <xsl:element name="field">
            <xsl:attribute name="name">department</xsl:attribute>
            <xsl:value-of select="./department/@name"/>
        </xsl:element>

       :q!

       <xsl:element name="field">
            <xsl:attribute name="name">categoryname</xsl:attribute>
            <xsl:value-of select="./class/@name"/>
        </xsl:element>

        <xsl:call-template name="addCurrentPrice">
        <xsl:with-param name="price" select="."/>
</xsl:call-template>

        <xsl:call-template name="addTaxonomyContent">
        <xsl:with-param name="skid" select="."/>
</xsl:call-template>

    </doc>
</xsl:template>

<xsl:template name="addCurrentPrice">
    <xsl:param name="product"/>

      <xsl:for-each select="./offers/offer/prices/*">
          <xsl:element name="field">
               <xsl:attribute name="name"><xsl:value-of select="@type"/></xsl:attribute>
               <xsl:value-of select="@amount"/>
        </xsl:element>
      </xsl:for-each>
</xsl:template>

<xsl:template name="addTaxonomyContent">
    <xsl:param name="product"/>
      <xsl:for-each select="./hierarchy/*">
           <xsl:variable name="ctr" select="position()"/>
           <xsl:variable name="variable" select="concat('tname_',$ctr)"/>

          <xsl:element name="field">
               <xsl:attribute name="name"><xsl:value-of select="$variable"/></xsl:attribute>
               <xsl:value-of select="@name"/>
        </xsl:element>
      </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

Sunday, March 02, 2014

Sloppy Kohl's sale sign

After moving to Austin, TX, this is 2ed time, I visited nearby Kohl’s location (zip code 78759)

During first visit, during December last week, I noticed store was very sloppy. I thought it is because of peak sales season. ( I was comparing w.r.t. Kohl’s Minneapolis stores.)

Yesterday, I went buy shoes for my Saketh. Still store is sloppy. few customers.

See the following picture. (Sale price is greater registered price.)

I keep getting too many e-mails/ physical mails mentioning about sales however, at store level their execution is very poor. May be it is time, Kohl’s management needs to slow down on sales promotion & focus on basic things.

Wednesday, February 26, 2014

Personal stock portal… YQL experiments.

In general, Lots of portals provides stock quote information. For example, @finance.yahoo.com, one can give a valid stock ticker & gets the stock information. From this point, if you want to know more about company profile you have to click one link or one more web service call. If you want to know more about last year statistics, you have to click or one more web service call. Couple of years back, after knowing little bit of YQL, I did small POC. Basically given a stock ticker, system will display complete snapshot of the company in one click. It is a fun ride however with all bug in YQL, It remained as POC only.

Tons of code. But I will dump few important methods.

////

String stockSym = "AAPL";

SupplierSection StockInfo = new SupplierSection(stockSym);

StockInfo.createHttpClient();

StockInfo.crawlFinanceInfo(stockSym);//crawlRssInfo

StockInfo.crawlProfileInfo(stockSym);

StockInfo.crawlKeyStats(stockSym);

StockInfo.crawlRssInfo(stockSym);

StockInfo.crawlQuantInfo(stockSym);//crawlRssInfo

//StockInfo.printAllPublicInfo();

try {

//StockInfo.writeToXmlFile();

//StockInfo.writeToXmlFile1(stockSym);

StockInfo.writehtmlFile1(stockSym);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

///basic compnay info

public void crawlProfileInfo(String stockSym) {

CloseableHttpClient httpclient = HttpClients.createDefault();

try {

GetMethod httpGet = new GetMethod("http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.stocks%20where%20symbol%3D%22"+stockSym+"%22&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys");

httpGet.setFollowRedirects( true );

System.out.println("executing request in crawlProfileInfo " );

int responseCode = httpClient.executeMethod(httpGet);

byte[] data = httpGet.getResponseBody();

if (responseCode >= 400) {

System.out.println("Failed to send request "+ responseCode);

}else{

String xmlString = new String( data, ENCODING );

//System.out.println("xml string "+ xmlString);

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();

DocumentBuilder db = factory.newDocumentBuilder();

InputSource inStream = new InputSource();

inStream.setCharacterStream(new StringReader(xmlString));

Document doc = db.parse(inStream);

String fName = "CompanyName";