LDAInference Example - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.10
1.1
Published
October 2019
Language
English (United States)
Last Update
2019-12-31
dita:mapPath
ima1540829771750.ditamap
dita:ditavalPath
jsj1481748799576.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

Input

  • InputTable: complaints_testtoken, a tokenized, filtered, input file created by applying the TextParser function to complaints_test, a log of vehicle complaints
  • Model: ldamodel, output by LDA Example
InputTable: complaints_test
doc_id text_data
1 ELECTRICAL CONTROL MODULE IS SHORTENING OUT, CAUSING THE VEHICLE TO STALL. ENGINE WILL BECOME TOTALLY INOPERATIVE. CONSUMER HAD TO CHANGE ALTERNATOR/ BATTERY AND STARTER, AND MODULE REPLACED 4 TIMES, BUT DEFECT STILL OCCURRING CANNOT DETERMINE WHAT IS CAUSING THE PROBLEM.
2 ABS BRAKES FAIL TO OPERATE PROPERLY, AND AIR BAGS FAILED TO DEPLOY DURING A CRASH AT APPROX. 28 MPH IMPACT. MANUFACTURER NOTIFIED.
3 WHILE DRIVING AT 60 MPH GAS PEDAL GOT STUCK DUE TO THE RUBBER THAT IS AROUND THE GAS PEDAL.
4 THERE IS A KNOCKING NOISE COMING FROM THE CATALYITC CONVERTER ,AND THE VEHICLE IS STALLING. ALSO, HAS PROBLEM WITH THE STEERING.
5 CONSUMER WAS MAKING A TURN ,DRIVING AT APPROX 5- 10 MPH WHEN CONSUMER HIT ANOTHER VEHICLE. UPON IMPACT, DUAL AIRBAGS DID NOT DEPLOY . ALL DAMAGE WAS DONE FROM ENGINE TO TRANSMISSION,TO THE FRONT OF VEHICLE, AND THE VEHICLE CONSIDERED A TOTAL LOSS.
6 WHEEL BEARING AND HUBS CRACKED, CAUSING THE METAL TO GRIND WHEN MAKING A RIGHT TURN. ALSO WHEN APPLYING THE BRAKES, PEDAL GOES TO THE FLOOR, CAUSE UNKNOWN. WAS ADVISED BY MIDAS NOT TO DRIVE VEHICLE- WHEELE COULD COME OFF.
7 DRIVING ABOUT 5-10 MPH, THE VEHICLE HAD A LOW FRONTAL IMPACT IN WHICH THE OTHER VEHICLE HAD NO DAMAGES. UPON IMPACT, DRIVER'S AND THE PASSENGER'S AIR BAGS DID NOT DEPLOY, RESULTING IN INJURIES. PLEASE PROVIDE FURTHER INFORMATION AND VIN#.
8 THE AIR BAG WARNING LIGHT HAS COME ON. INDICATING AIRBAGS ARE INOPERATIVE.THEY WERE FIXED ONE AT THE TIME, BUT PROBLEM HAS REOCCURRED.
9 CONSUMER WAS DRIVING WEST WHEN THE OTHER CAR WAS GOING EAST. THE OTHER CAR TURNED IN FRONT OF CONSUMER'S VEHICLE, CONSUMER HIT OTHER VEHICLE AND STARTED TO SPIN AROUND ,COULDN'T STOP, RESULTING IN A CRASH. UPON IMPACT, AIRBAGS DIDN'T DEPLOY.
10 WHILE DRIVING ABOUT 65 MPH AND THE TRANSMISISON MADE A STRANGE NOISE, AND THE LEFT FRONT AXLE LOCKED UP. THE DEALER HAS REPAIRED THE VEHICLE.

This example uses this stop words file, stopwords.txt:

a
an
in
is
to
into
was
the
and
this
with
they
but
will

To create a tokenized, filtered, input file for the LDAInference function, apply the TextParser function to complaints_test, using the file stopwords.txt:

CREATE MULTISET TABLE complaints_testtoken AS (
  SELECT * FROM TextParser (
    ON complaints_test
    TextColumn ('text_data')
    ConvertToLowerCase ('true')
    Stemming ('false')
    ListPositions ('true')
    StopWordsList ('stopwords.txt')
    RemoveStopWords ('true')
    Accumulate ('doc_id') 
  ) AS dt
) WITH DATA;

SQL Call

SELECT * FROM LDAInference (
  ON complaints_testtoken AS InputTable
  ON ldamodel AS Model
  OUT TABLE OutputTable (ldaout2)
  USING
  DocIDColumn ('doc_id')
  WordColumn ('token')
  OutputTopicNum (5)
  OutputTopicWordNum (5)
) AS dt;

This query returns the following table:

SELECT * FROM complaints_testtoken ORDER BY doc_id;
complaints_testtoken
doc_id token frequency position
1 electrical 1 0
1 control 1 1
1 module 2 2,25
1 shortening 1 4
1 out 1 5
1 causing 2 6,37
1 vehicle 1 8
1 stall 1 10
1 engine 1 11
1 become 1 13
1 totally 1 14
... ... ... ...

Output

 message                                                                                           
 ------------------------------------------------------------------------------------------------- 
 There are 10 valid documents with 153 recognized words in the input, the perplexity is 146.040212
 Outputtable is created successfully. 
SELECT * FROM ldaout2 ORDER BY docid, topicid;
 docid topicid topicweight          topicwords                                      
 ----- ------- -------------------- ----------------------------------------------- 
     1       0 0.004202361754413823 wipers,would,switch,when,on                    
     1       1   0.9818923737738922 vehicle,causing,consumer,replaced,which        
     1       2 0.004507796811204752 vehicle,manufacturer,would,transmission,problem
     1       3 0.004313653081088213 did,not,deploy,hit,vehicle                     
     1       4 0.005083814579401286 vehicle,side,car,engine,while                  
     2       0  0.23870769504862557 wipers,would,switch,when,on                    
     2       1 0.013331851654654326 vehicle,causing,consumer,replaced,which        
     2       2  0.09802669464453168 vehicle,manufacturer,would,transmission,problem
     2       3   0.6321839644685451 did,not,deploy,hit,vehicle                     
     2       4 0.017749794183643395 vehicle,side,car,engine,while                  
     3       0  0.02487023130390702 wipers,would,switch,when,on                    
     3       1  0.38939182929425087 vehicle,causing,consumer,replaced,which        
     3       2  0.22880351701600884 vehicle,manufacturer,would,transmission,problem
     3       3  0.17393893594698806 did,not,deploy,hit,vehicle                     
     3       4   0.1829954864388453 vehicle,side,car,engine,while                  
     4       0 0.017412050629395465 wipers,would,switch,when,on                    
     4       1  0.02674751176357119 vehicle,causing,consumer,replaced,which        
     4       2   0.6146553050991005 vehicle,manufacturer,would,transmission,problem
     4       3 0.016502037137102715 did,not,deploy,hit,vehicle                     
     4       4   0.3246830953708302 vehicle,side,car,engine,while                  
     5       0 0.061682117690179554 wipers,would,switch,when,on                    
     5       1 0.007226496686956611 vehicle,causing,consumer,replaced,which        
     5       2  0.07038616515924828 vehicle,manufacturer,would,transmission,problem
     5       3   0.7114242472226594 did,not,deploy,hit,vehicle                     
     5       4  0.14928097324095613 vehicle,side,car,engine,while                  
     6       0  0.13471826421661065 wipers,would,switch,when,on                    
     6       1  0.47822125749783323 vehicle,causing,consumer,replaced,which        
     6       2  0.05589848757873593 vehicle,manufacturer,would,transmission,problem
     6       3   0.2854434939021821 did,not,deploy,hit,vehicle                     
     6       4 0.045718496804638104 vehicle,side,car,engine,while                  
     7       0 0.007096264886642341 wipers,would,switch,when,on                    
     7       1   0.2897611972252564 vehicle,causing,consumer,replaced,which        
     7       2 0.007262190348990799 vehicle,manufacturer,would,transmission,problem
     7       3   0.6854073542763487 did,not,deploy,hit,vehicle                     
     7       4 0.010472993262761772 vehicle,side,car,engine,while                  
     8       0  0.18451313388726223 wipers,would,switch,when,on                    
     8       1   0.2594229641285835 vehicle,causing,consumer,replaced,which        
     8       2  0.03883152533036032 vehicle,manufacturer,would,transmission,problem
     8       3   0.3403368717029619 did,not,deploy,hit,vehicle                     
     8       4  0.17689550495083203 vehicle,side,car,engine,while                  
     9       0  0.10716645608023352 wipers,would,switch,when,on                    
     9       1    0.011237010500215 vehicle,causing,consumer,replaced,which        
     9       2 0.012090442989372539 vehicle,manufacturer,would,transmission,problem
     9       3   0.5304589100317839 did,not,deploy,hit,vehicle                     
     9       4  0.33904718039839515 vehicle,side,car,engine,while                  
    10       0  0.10908056835555743 wipers,would,switch,when,on                    
    10       1   0.1611027170455575 vehicle,causing,consumer,replaced,which        
    10       2  0.13454850444953406 vehicle,manufacturer,would,transmission,problem
    10       3   0.1412092067930684 did,not,deploy,hit,vehicle                     
    10       4   0.4540590033562826 vehicle,side,car,engine,while

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.